Record Numbering in Query

[Wayne Emminizer]

I have a dataset with a few hundred thousand records.
What I need to do is assign a unique line number to each
record and have that numbering start over each time the
invoice number change changes. So, if there are 10
invoices with three lines on each I need to assign values
1-3 respectively (order doesn't really matter) to the 3
records and once that invoice is out of records and moved
to the next invoice I need to start that numbering over
again. I am trying to clean up legacy data. Any help
would be appreciated. Thanks in advance.

 

[Ken Snell [MVP]]

Something like this (very generic, as I don't know what fields are in the
table):

SELECT TableName.*,
(SELECT Count(*) FROM
TableName AS T
WHERE T.InvoiceNumber =
TableName.InvoiceNumber AND
T.SomeOtherField <= TableName.SomeOtherField)
FROM TableName
ORDER BY TableName.InvoiceNumber;

 

[Van T. Dinh]

Since you taked about the invoice line number, this can easily be done in
Access Reports rather than creating this number in the Query.

Check the "Running Sum" Property of the TextBox (when used in a Report).