We have a really cool query that we run to re-format data coming from an AS400 program that we then import into a Windows based program.
It is working very well except for one thing.
Each time we run the query we use a different file for the raw data. We have an auto increment field that does not "zero out" each time the query is run--it remembers the last time it was run and increments from there.
We want to have the records numbered from 1 to X (X = number of records in the query). The next time we run the query, we want to start over again at 1. This seems very simple--any ideas out there?
The only way that I know of to reset the autonumber in the table you are putting the data is to delete all data out of the table, compact your database and then it will start at zero. If you can't remove all data out of the table you need to create your autonumber in the query itself and not let the table do it.
to get a working example of how to autonumber within a query (rank your query), goto micorsoft knowledgebase and search for:
This sample DB from MS has a query that ranks yoru records (will create a number scheme the will number your records starting at 1)