Reset Autonumber to Start at 1

[Susan]

I'm importing data from Excel that has been gathered from
several different laptops into a Temporary table in
Access. The data structure is all the same and filename
is the same. When the data comes in, I want to set
an "AutoNumber" field to keep the records in exactly the
same order as they are imported. After one spreadsheet
has been processed and the Temporary table cleared, I
want the "AutoNumber" field to RESTART at Number 1 each
time I import a new spreadsheet so I can query out of my
final table all the records with an "AutoNumber" less
than 739. I need for all of the first records to be #1,
second to be #2, etc.

Is this possible, and if so, how can I make this work?
Thanks so much for any help!

 

[GVaught]

Autonumber shouldn't be used for this purpose. Autonumber is a datatype
designed to be used to set a primary key against a table where any other
field can't be used. If you plan to repeat numbers use a different datatype.

What is the purpose of this table where you need to repeat the numbers? What
type of relationship will this table have with any other tables in the
database? How will you maintain referential integrity?

 

[Susan]

This is an inter-company auditing program. When the data
comes in, it is in Excel with the primary audit as the
first 738 records, and supplemental data as the next 300
records. When I bring it in, I want to put ALL records
into one table, then pull out only the ones marked
unsatisfactory from the first 738. Since they always
come in as the same order, I wanted to "autonumber" them
the same way and use that as criteria to select the
records to pull out and process further.