Assign sequential number to specific records

[Guest]

I have a table where I want to assign a sequential number to specific records
starting with 100. What is the best way to do this and how?

Many thanks.
Meryl

 

[Marshall Barton]

I have a table where I want to assign a sequential number to specific records
starting with 100. What is the best way to do this and how?

Identify the fields and values that can be used to specify
the records that need to be changed.

Spcify the fields and values that can be used to determine a
unique sort order for the records to be changed.

Decide if the sequential numbers will ever be changed and
under what conditions. If they might need to be renumbered
fairly often, then use a calculated field in a Select query
to renumber the records on the fly instead of using a field
in the table.

If they need to renumbered only under very unusual
circumstances, you can safely(?) save the numbers in the
table using an Update query.

 

[Guest]

If you are asking about adding the numbers to existing records in the table,
Marsh's answer will work. If, on the other hand, you are asking for a
technique to add sequential numbers to new records you are adding, then you
will need to determine what the next number is and whether the record meets
the criteria to have the number added. For this, you can use the DMax
function to determine the highest existing number and add 1 to it:

intNextSeqNum = Nz(DMax("[SeqNumFld]", "AnyOleTable", "Criteria"), 0) + 1

Critera would be the rules to determine which records are included.

 

[Guest]

The sequential numbers will not be changed. I just don't know how to assign
the numbers in an update query. What is the formula?

Thank you.
Meryl

 

[Marshall Barton]

The "formula" depends on the fields that are used to
identify the records to be updated AND on the field that is
used to specify the unique sort order. The calculated field
uses a subquery along the lines of a list of purchase order
details:

SeqNo: (SELECT Count(*) FROM table As X
WHERE X.OrderID = table.OrderID
And X.item <= table.item)