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?

[June7]

What is the process - you import all thousand records into a table then insert only 10 of those records into another table? The sequence number should be assigned in that second table - the autonumber from the initial import can be ignored. What happens to the other 990 records - deleted?

An expression using DCount can populate a field with sequential number but you need to consider the maximum value of existing records - doesn't sound easy.

[jobrien4]

Maybe this is where you were getting at with the DCount but someone in another forum answered this. You need a unique number (in the example below Order Number). You create a query that sorts the data by the unique number then use expession:

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.