autonumber column

[BG]

Hi-I'd appreciate if anyone can help on this.

I am trying to create a query that inserts the top25
results to a table with an autonumber column for ranking
purpose.

The query needs to be run once every week. I have a
delete query that runs first and then appends the results
but the autonumber starts from 26 second time I run the
query.

Is there a way to set the autonumber to start from 1
every time I delete the rows and insert again?

Or is it possible to create a make table query that will
also create a autonumber column at the same time so every
time the query is run it will show from 1 to 25?

Please let me know if any one can help.
Thanks

 

[Allen Browne]

Using ADOX, you can set the Seed property of the autonumber column.

 

[Tim Ferguson]

I am trying to create a query that inserts the top25
results to a table with an autonumber column for ranking
purpose.

This is really not a good idea for an autonumber. There is no guarantee
that the numbers are going to be sequential (although there is relatively
small chance of getting missing ones in this particular scenario); and
there is no guarantee that they will sequence in the order you want. A
future version of the Jet engine may well not allocate ANs to a query while
the source is still sorted (if you see what I mean).

You would be safer creating the table either with a proper ranking query,
or else creating the new table first and then using an UPDATE query to
populate the ranking column.

Just a thought


Tim F