Hello to all. I am new to this forum and have a problem that I am sure has an easy fix. I have a table of invoices that contains duplicate invoices. (There should be no duplicate invoices.) I have a query that identifies only the duplicate values, and a row exists for each duplicate invoice showing the different statuses (causing the duplicate). What I need to do is create a query to:
1) Add a field to the query to generate a sequential number for each record returned. Like an autonumber in a table. See ID in below table.
2) Add a field to the query to generate a second sequential number that resets to 1 after each change in the Invoice number. (See Sequence in below table.)
To simplify this, I have described the query results I am looking for in the below in a table. ID and Sequence number are what I need to create.
1) Add a field to the query to generate a sequential number for each record returned. Like an autonumber in a table. See ID in below table.
2) Add a field to the query to generate a second sequential number that resets to 1 after each change in the Invoice number. (See Sequence in below table.)
To simplify this, I have described the query results I am looking for in the below in a table. ID and Sequence number are what I need to create.
ID | Invoice | Sequence | Status |
1 | 222222 | 1 | Open |
2 | 222222 | 2 | Closed |
3 | 333333 | 1 | Open |
4 | 333333 | 2 | Closed |
5 | 333333 | 3 | <No Status Found> |