Queries :: Allocate A Record To A Place In Table

Nov 25, 2014

I've got a table [Devices] with some number fields that are already in there for another purpose.

In a different exercise I will manually collect more information from a layout drawing and give each piece of information I collect a unique reference(this could be as simple as a PK ref) and attach a "type" and "location" . This will be done randomly in a batch exercise any time.

I need to put the above into access and hold it until I choose to individually allocate these records to the number field that already exists in the [Devices] table.

I would like to add "say" a combo/List box in [Devices] where I could search for the PK Ref which when selected against a number would populate the [Devices] table with previously unallocated information. I would like to keep any combo/List box down to unallocated PK refs to make records easy to find. I guess this a kind of cut and paste exercise.

View Replies


ADVERTISEMENT

Queries :: Selecting One Record And Place It On Report?

Jun 26, 2013

After running a query is it possible to select just one record and place it on a report or print it? If so how do i go about it?

View 3 Replies View Related

Allocate Record By User

May 15, 2006

Hi All

I want a user to click a button and then it will assign the oldest record in the table to that particular user, by using their NT login.

Anyone got any suggestions?

Thanks

View 5 Replies View Related

General :: Allocate Record Number To End User?

Aug 18, 2014

What is the best way to allocate a record number to the end user?

I have had problems where users have come back to me saying that both of them created the same record number at the same time and I am not sure why that is happening.

To be certain, I am using INSERT rather than UPDATE which was my initial concern (an update would just over write the previous record)

How can we check the last value, uniquely assign that value to a person at the point of creating the record and ensure that no one else can take it...

Is it possible that a laggy server would cause these issues? (as the code executes roughly the same time as another person also executes code, they overlap?)

View 1 Replies View Related

Queries :: Script To Do Calculation For Every Record And Place Result As Field In Query

Dec 21, 2013

1. I created a table that contains information about people and their details (mainly numerical info).
2. I created a form containing a command button and a label.
3. I have written a VBA script under the button so that when the button is pressed, the result of the calculation appears as the caption on the label.

My problem is...How do I get the script to run so it does the calculation for every record and places the result as a field in a query.

View 2 Replies View Related

Queries :: Allocate Annual Sales By Month And Work Day

Dec 1, 2014

On the surface this seems easy, but I am struggling. two tables

tbldata (four fields 6,270 rows)
custid 2015cellcode 2015qty 2015amt
12673 1243 100 1,000.00
12673 1250 200 2,000.00
etc etc

tblmntlyalloc (Three fields and 12 rows i.e. one row for each month)
2015mnth, 2015allocation, 2015wrkdays
jan 98 20
feb 93 19
etc etc etc

So in order to handle seasonality of sales, the sales department is given 1,200 points. you can then allocation any number of points (75 - 125) per month. But the total number of points must equal 1,200. So I created a table with the monthly allocation and workdays. I could hard code the % and work days into the formula like this

Jan1[2015amt]*(98/1200)) / 20
Feb1[2015amt]*(93/1200)) / 19

And this does work very well to get me my daily sales amount per month/wrkday

However I know sales will change the monthly allocation (still totals 1200) and HR could even have a change to the holiday schedule. So I want to utilize the table.

So my query brings in both tables, but they are not linked. And this is my formula.

Jan1[2015amt]*([2015allocation]/1200) / [2015wrkdays]
Feb1[2015amt]*([2015allocation]/1200) / [2015wrkdays]
Mar1[2015amt]*([2015allocation]/1200) / [2015wrkdays]
Apr1[2015amt]*([2015allocation]/1200) / [2015wrkdays]
etc etc for 12 months

So I need it to find the value for Jan in the Jan formula, and Feb in the feb formula etc. I cannot determine the correct query formula.

View 8 Replies View Related

Queries :: Can Place Criteria In Calculated Field?

Jan 27, 2015

Can I place a criteria in a calculated field?

[SellingWgt]*[SellingPrice] is ok but only
if [SellingUnits] = "lbs" or [tblSellingUnits].[SellingUnitsID] = 1

View 7 Replies View Related

Queries :: Place Number With Text Selection

Mar 12, 2014

I have a table with a field name compliance level which is filled with drop down list and having like this;

poor
good
excellent
IR

Now what I want is if i select poor from list then in query it generate -10 in query field named analysis, same if select good then generate 10 and so on means i will assign numbers for each field...

One can select only one item like, poor at one record entry...

View 4 Replies View Related

Queries :: Criteria Containing A Letter In A Specific Place In Sequence

Oct 11, 2013

How to sort a field of data in a table that contains the letter S that is always in a specific place in the sequence? For Example:

13123S0-BAX
13124E1-ARZ
13122X2-THX
12134S0-GAB

View 2 Replies View Related

Queries :: Exporting Currency Field Without Decimal Place?

May 1, 2015

I need to export a currency field to include the pence but without the decimal place, is this possible?

It also needs to be 11 characters wide.

I have tried a number of formats and can get the decimal point removed but I am not getting the pence displayed.

View 5 Replies View Related

Automatically Allocate Stock?

Aug 25, 2005

I have a database for taking orders for books, leaflets etc, what i want to do is this:

When I receive a delivery of stock I enter how many of each ‘Product’ I have received, I then want Access to allocate these to the various orders to make then ready for collection.

For example, if five people have all ordered 3 of ‘Product Guide’ and I only receive 15 then the first three to order are allocated three each.

Not got a clue how to do this, guessing I’ll need to code it?

Hope it makes sense

View 4 Replies View Related

How To Allocate Percentages Of A Number Across Different Fields

Jul 27, 2012

We have a model at my job which shows every job code one can do (there are only about 25 of these jobs) For example, let's say one job is called "Trade Settlements" and it's been estimated that we spend about 1000 minutes a day doing this particular job across the entire floor.

Well, I want to come up with a way to allocate the minutes of this to certain hour blocks and determining whether our group is over/understaff given the results.

So let's say I want, 30% of it to be done from 9-10 am, 10% done over the next 4 hour time blocks, and 2-3 pm for the remaining 30%.

Thus, we'd get something something like 300, 100, 100, 100, 100, 300 minutes and 0's for other time blocks.

These percentages would be input fields so if anyone types in a percentage, they get # of minutes spent for that hour on that job. Ultimately we would add these numbers up with other jobs and be able to easily update from there for any job type we wanted.

View 1 Replies View Related

Queries :: Drop Down Access Standard Menu To Place Count Command

May 29, 2014

I have been using a table with queries with no problems. Now the queries wont recognize the last two records of the table when doing a Count. there are no null or empty spaces. I am using the drop down access standard menu to place the "Count" command. Is there a solution to this problem ?

View 3 Replies View Related

Queries :: Using Query To Place Single Grand Total On Row Based On Particular Criteria?

Dec 7, 2014

I have a very simple query on an accounts form to show a running transaction history.

Identifying from the TransactionID (shown for display purposes only - normally hidden) three or four postings make up one transaction.

Using TransactionID 10 as an example, I'd like to have a sum of total [Credit]-[Debit] and have the query display on one line (either at the top of £1,429, or at the bottom of £16,995) to identify this is in fact one transaction, having three posts.

Transaction 9 will have obviously have one total, as this is a single post.

11 the same as 10, by having one total Transaction Value either at the top of the row or bottom.

View 4 Replies View Related

Problems With Decimal Place In Query, Table And Report

Aug 9, 2007

i want my data to have only 2 decimal places. So i change the property of the table to only allow 2 decimal places and i did the same to the report and they are still all over the place....for example a field would say 8.12233444 and another 1.17.....what do you think is the problem?

View 3 Replies View Related

Queries :: Add Multiple From One Table To A Single Record On Another Table

Aug 1, 2013

I have two tables

tbl_Orders
Cust OrderNo OrdLne Prod Qty
ABC SO123 1 AA 15
ABB SO124 1 AB 10

tbl_StockLoc
Prod Loc Qty
AA ZX12 10
AA ZZ12 10
AA ZZ13 50
AB CW12 15

One product can have up to 10 locations in the tbl_StockLoc

I need to print the following report (up to LOC10)

Cust OrderNo OrdLne Prod Qty Loc1 Qty1 Loc2 Qty2 Loc3 Qty3 ....
ABC SO123 1 AA 15 ZX12 10 ZZ12 10 ZZ13 50
ABB SO124 1 AB 10 CW12 15

View 1 Replies View Related

Queries :: Return Last Record In A Table

Sep 23, 2014

My setup is simple, it consists of two tables:

Employee (one)
Position (many)

So an employee in this database can have many different positions. The tables are linked using an EmployeeID field.

I want to construct a query that will list each employee and the last job entered for them in the database. Right now my query simply returns all the positions held (where there are more than one)?

View 10 Replies View Related

Queries :: Delete Max Record From A Table

Jul 23, 2015

I have table1 that I append data to. The unique_ID is the rpt_date.

On form1 (that shows the table1 data) I want to delete the Max rpt_date from the table1.

I have tried to create a query involving MaxOfrpt_date linked to the table1 by the rpt_date and then create a delete query but it doesn't work !!

View 1 Replies View Related

Queries :: Lookup At The Previous Record In The Table

Jan 25, 2014

I've got stuck in preparation of this sales query. The primary sales table contains a mix of Canadian and the US detail invoice sales records. All sales records are in their native currencies The secondary lookup table contains daily US/CAN foreign exchange rates (FX).

I need to multiply every $US sales record (marked with U) with the FX rate in order to convert it to the Canadian currency. Unfortunately, this FX lookup table is missing over 50 daily rates in the last two years and as a result I cannot match them date-by-date with the sales table.

As a workaround, it is acceptable to use the previous FX rate in the table. The previous rate can be one or more days before the transaction occurred.

Attached is a sample database with the query that I've already built. The query contains two sample US records that are missing the FX rate on Jan 6. The FX rate of $1.0639 that needs to be applied to it should come from Jan 3rd entry.

New Datesales matcode curtype trans newrate
1/3/20141225.61281173224U R187611.0639
1/3/2014344.70361173260U R181731.0639
1/6/20142520 0022691U R19841
1/6/20145400 0022692U R19841

View 14 Replies View Related

Queries :: Transaction Table - Find Last Record For Each Day

Oct 7, 2013

I have a table that contains records of transactions. I need to find the last record for each day.

DBTransIDtransDay UID
1931219/17/07 8:50 AM128
1932879/17/07 9:13 AM128
1932929/17/07 9:14 AM128
1933049/17/07 9:16 AM128
2388149/18/07 4:54 PM128
2388449/18/07 5:06 PM128
2388459/18/07 5:06 PM128
2388469/18/07 5:07 PM128
2389299/18/07 5:45 PM128
2389309/18/07 5:45 PM128
2389319/18/07 5:45 PM128
2391299/19/07 8:55 AM128
2391389/19/07 8:57 AM128
2391399/19/07 8:57 AM128
2391409/19/07 8:57 AM128
2391419/19/07 8:57 AM128

A script that I found that appeared to do what I needed it to do:

SELECT [TT Transactions - KT].DBTransID, [TT Transactions - KT].transDay
FROM [TT Transactions - KT]
JOIN (SELECT MAX(transDay) Max_transDay_By_Day
FROM [TT Transactions - KT]
GROUP BY convert(varchar, transDay, 112)) t2
ON t1.transDay = t2.Max_transDay_By_Day

But it returns a "Syntax error in FROM clause" that I can't figure out.

View 2 Replies View Related

Queries :: How To Keep Only The Most Current Date Record In Table

Mar 8, 2014

I have this table that records sales events for properties, with multiple sales records for some parcels. Each parcel has unique field: MapTaxlot. I want to create a select query or make table query that shows only the most recent sale event for each parcel. Instrument_Date is the date field for the sales records.

See attached example of the data table below.

What is the simplest method to accomplish this?

TableSnip.JPG

View 5 Replies View Related

Queries :: Unique Record Table Query

Oct 22, 2013

I am using a software drawing program that intelligently link information from a drawing to a database and vice versa. I have many tables and queries that I have created over time that enable auto populating of tables, creation of tables and so on. All of which enable me with tabular documents for ordering purposes.

The problem I currently have is: - I want to create a table that looks at an existing table, analysis various fields for duplicate information and then adds one record to a new table. This is to enable me to create a spares list based on the entire list.

I have a full valve list with various items, some duplicates some not. I am required to produce a spares list that considers only one of each type.

View 10 Replies View Related

Queries :: Combine Several Records In A Table Into One Record

Jan 22, 2014

How can I combine several records in a table into one record?

Suppose that I have a table like Table1 in the attached image.

Then I want to combine all records with the same value for Key1 in one record.

The result is shown in Table2 in the attached image.

I would prefer to do it using SQL only, but I guess that this is not possible. Is it possible?

Alternatively I could accept to turn to VBA that could do it. Any good links about this?

View 3 Replies View Related

Queries :: Return Latest Record In Table

Feb 23, 2014

I'm trying to create in access2010

(1) a query that returns the latest record (newest) in a table called 'Invoices' and then

(2) places this value in a form called 'FrmInputInvoices' as the default value when the form opens. Newest record is by Autonumber and the table defaults this to top of table as views newest down to oldest.

Re (1) Query is called 'QInvoices'; the values I want to return in my query is ID (my autonumber) and Invoice_No . Must be a simple answer to put in the criteria, but I can't find this.

Re(2) What code do I use in my Form field named 'Invoice_No when the curser defaults there on opening?

View 4 Replies View Related

Queries :: Append Just Current Record Into A Table

Jul 17, 2014

I am trying to open a form with some records taken from one table, then alter couple of data in it and after that most importantly to append current records into a different table. The reason I need to append is that I need to keep track on every occurrence on same fields.

It looks like this:

SerialNumber(Field 1), ServiceDate(Field 2), ServiceEngineer(Field 3)

Now suppose same serial number called again and asked for service, I need to still keep record on how many times this same serial number had service.

View 9 Replies View Related

Queries :: Fields Populated For Each Record From The Table

Nov 2, 2014

I have a table with multiple fields and each record in the table may not have data for all fields. I need to write a query that will pull only the fields that are populated for each record from the table.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved