Incremental field on query

[jobrien4]

I'm building an application via Access that will allow a user at my company to import a CSV file with hundreds of records, press a button that runs several insert queries which inserts some of those records into our ERP system based on criteria.

Each record of the insert query must have a unique "Interchange Number" that is historically unique, ie, if I used number 1000 in the insert query yesterday, I would need to use 1001 today.

I already can find which value to start with using a DMAX() function on the table where you insert the records.I need a way to give each record of a select query a unique value that is +1 from the preceding record.

When I import the CSV file, I already have Access assigning an Autonumber. The problem is the file can have up to a thousand records a day while I only need to insert maybe 10 each day. If the starting Interchange number was 1001, I would like it to go 1001, 1002, 1003, etc. If I add the Autonumber from the table I imported, it can go 1001, 1050, 1343, based on where that record was on the autonumber when it imported.

So is there anyway to run a select query and have a field on that query start at 1 and count up by 1 for each record? If I can get that I can code the rest. Any ideas?

 

[plog]

So is there anyway to run a select query and have a field on that query start at 1 and count up by 1 for each record?

Yes, but only if you can uniquely order the results of your query. The key term being "uniquely order". To do this solely in one query, you need a way to order your data such that there is no ambiguity. In practice this means orderying by a primary or composit key.

You didn't provide data, so lets use this set to show you what I mean:

YourTable
HireDate, LastName, FirstName
12/1/2014, Smith, John
12/2/2014, Smith, Shelly
12/1/2014, Jones, David
12/5/2014, Baker, Steve

That data set cannot be uniquely ordered. If you order it by HireDate, you have 2 12/1/2014 values; order by LastName, you have 2 Smiths. Even if you order by HireDate, then by LastName, then by FirstName you can't guarantee you won't ever get ties because you might hire 2 John Smiths on the same date. If your data is like the above, you cannot do what you want in a query alone.

If that data had a Social Security number (or whatever you guys across the pond use), you could order the data uniquely because you could order by SSN since you know its unique.

So does your data have a unique key field (or a composit key field--2 or more fields that make every record distinct)?

 

[jobrien4]

Yes it does appear to have a unique field called [OrderNumber]. The data in that field all begins with a P then has about 9 numeric digits. As far as I can tell no two records have the same OrderNumber.

Assuming it is unique identifier, how can I take that and create a field in the query that goes 1, 2, 3...n for n number of rows?

 

[plog]

You build another query using your query as the source. Bring in all the fields from your query and then an additional calculated field using this:

RecordNumber: DCount("[OrderNumber]", "YourQueryNameHere", "[OrderNumber]<='" & [OrderNumber] & "'")

Replace "YourQueryNameHere" with the name of your query. It basically counts the number of records equal to or before it. That way it knows what position it holds within that order. Follow my instructions, look at the data, and let me know if you have any issues.

 

[jobrien4]

That worked. Thanks for your help