numbering the records in a query result

[scott6117]

Is there a way to number the results of a query? I import data into a table each week and then generate a query based on the table. I need the results of the query to be numbered from 1 to XXXX. I've tried to use the autonumber datatype in the table but the count doesn't reset to 1 each time the new data is imported. Reseting the autonumber field in the table would also work.

Thanks
Scott C.

 

[dodge20]

see if this helps

faq701-1155

Dodge20

 

[tledwards]

How about creating a new table each time instead of appending to an old table? Each time you get done delete the table. Then import the data to a new table with the same name.

 

[substitute]

The incrementing function is a good idea, but I like to do things in the query where possible. You could create a counter field as something like:

counter: [id]-dfirst("[id]","

&quot + 1

That is pretty fast and is easier for others to understand.

You can also reset the autonumber field by emptying the table and then compacting the database, but that is a manual process as Access closes the database when you compact it.