How to add sequential numbering ?

[William Poh Ben]

Hi there,

If I don not want ACCESS to automatically add the primary ID for the
column of PartNumbers, but I want to use query to add the serial
numbers sequentially , do you know the query statement ?

An example as below.

Thanks for the help in advance !

S/N PartNumber
1 0010-04514
2 0010-04983
0010-05311
0010-08113
0010-08114
0010-08224

 

[SteveD]

I use a query to create my own key like your suggesting.
I have a table (tblKey) that stores the next key value. I
run an update query with the data table and the key
table. The two tables are not joined. The first field in
the query is to update the tblData.key with the
tblKey.value, the second field updates the tblKey.value
with tblKey.value + 1. You will need to have a criteria
to only update the specific record(s).

This way I can control where to start, I can have
different keys for different transaction types and I don't
need to worry about when compacting reindexes the
autonumber fields.

This routine works great.

HTH
SteveD