Ranking Usiing Autonumber

[Mike C]

Hello,

I have a function that deletes a bunch of tables, appends
data for perfect scores, and then appends again for those
with less than perfect scores. The records are appended
in the table in the order they should be ranked. There
are certain situations where more than one person can
achieve a ranking of 1 (perfect scores for example) and so
I was hoping to us auto number to increment by 1 for every
record appended into the table. This works fine the first
time, but when I delete the table and re-append again it
continues counting from where it left off.

Is there any way I can reset the auto number field to
start counting from 1 after I delete all the records in
the table?

Thanks in advance.

 

[Allen Browne]

It should reset if you compact the database.

Alternatively, you can use ADOX to set the Seed of the AutoIncrement column.
There is a (rather involved) example of this at:
http://members.iinet.net.au/~allenbrowne/ser-40.html

Another alternative would be to lookup the minimum value of the primary key
field using DMin(), subtract that and add one, so the displayed number
starts from 1.