How to add sequential numbering ?

  • Thread starter Thread starter William Poh Ben
  • Start date Start date
W

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
 
William said:
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
Click to expand...

If you are wanting to add S/N 3-6 to the PartNumbers that have no S/N in your
example, I suspect you cannot do this with a simple query. You will have to
do it in recordset code. You can use code to go through the table and update
the S/N field to the next number. You would have to capture the existing
number for a line that has an entry into a local variable, add 1 to it in
your local variable, then advance to the next record. If there is nothing
there, update the record. If there is something there, repeat for the next
record until you have gone through the whole table.

If this is a one time thing, I would output the table to excel, input the
numbers, and import the file back in. This would be much quicker.

gm
 
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
 
Back
Top