Sequential Numbering by Group

Question

I have a question regarding sequential numbering.

We use Access to generate our monthly billing.  The resulting data is then exported to our accounting software.  One item that the accounting system requires is a Reference Number, which needs to be in sequential order.

I understand how to create sequential numbering for each row of the resuting data, but I cannot figure out how to do sequential numbering over groups.

Using simple sequential numbering, the query delivers the following:

Reference     Date          Account #     Sign ID     Rent

1                   01/01/01    2208             KSE1        $10

2                   01/01/01     2208            KSE2        $10

3                   01/01/01     4500            KSE3        $10

4                   01/01/01     4500            KSE4        $10

5                   01/01/01     6200            KSE5        $10

What I am trying to do is create sequential numbering in the Reference field that will assign a number based upon Account #.  In other words, if the Account # is 2208, the Reference field will equal 1 for both entries.  The next unique account number will be assigned "2", etc.  This is necessary so that the Reference number in our accounting system matches the Reference Number on the customer's invoice.

I am trying to achieve the following results:

Reference      Date         Account #       Sign ID    Rent

1                    01/01/01   2208               KSE1       $10

1                    01/01/01   2208               KSE2       $10

2                    01/01/01   4500               KSE3       $10

2                    01/01/01   4500               KSE4        $10

3                    01/01/01   6200               KSE5       $10   

Each unique Account # will be assigned the same Reference number.

Thank you all very much for taking the time to look at this for me.

Answer 1

Try this:

SELECT

   (SELECT COUNT(*)

    FROM (SELECT DISTINCT [Account #]

                 FROM [YourTable]) AS T2

     WHERE T2.[Account #] <= T1[Account #]) AS Reference,

[Date], [Account #], [Sign ID], [Rent]

FROM [YourTable] AS T1

ORDER BY [Account #],[Date];

BTW using Date as a column name in a table is not recommended.  Date is the name of a built in function and as a 'reserved' word should be avoided for object names.  A more specifically descriptive term such as TransactionDate, or whatever is appropriate, is better.

Answer 2

You might also find my blog on sequential numbering helpful.

Answer 3

Ken,

Thank you very much for the quick reply!  Works perfectly.

The column named "Date" is actually "EffDate" - I just used a simplifed description for the illustration.  But thank you for the recommendation anyway.

Terry Gassaway

Answer 4

Try this:

SELECT

   (SELECT COUNT(*)

    FROM (SELECT DISTINCT [Account #]

                 FROM [YourTable]) AS T2

     WHERE T2.[Account #] <= T1[Account #]) AS Reference,

[Date], [Account #], [Sign ID], [Rent]

FROM [YourTable] AS T1

ORDER BY [Account #],[Date];

BTW using Date as a column name in a table is not recommended.  Date is the name of a built in function and as a 'reserved' word should be avoided for object names.  A more specifically descriptive term such as TransactionDate, or whatever is appropriate, is better.

Hi Ken,

I am trying to do a similar thing, but I would like to number each reference number sequentially starting at 1 each time when the account number changes.  I am using for manufacturing though.  Each job will have operations open and within the query I want to number each jobs operation with a sequential number starting at one.  When the job changes then the numbering will begin again.  There is a sequence number for each job operation that sorts it, but it is different for each job so that field I believe can only be used to sort the numbering function.

The date looks like this as an example:

1234	cr	1000	dust	20
1234	cr	2000	open	15
1234	cr	3000	dddd	45
12345	cv	1500	dddee	10
12345	cv	2500	adlkjf	20
12345	cv	2650	dlkjad	30
12345	cv	2700	ouoiuoi	67

I can put the job and suffix into one field but can I keep them separate and still get this to work.

Thanks for your help!

Mike

Answer 5

Take a look at RowNumbering.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

This little demo file includes illustrations of queries for numbering or ranking rows, including by group.  The most efficient method is by a JOIN of two instances of the table, for which the query in my demo is:

SELECT COUNT(*) AS RowNumber, T1.CustomerID,

T1.TransactionDate, T1.TransactionAmount

FROM Transactions AS T1 INNER JOIN Transactions AS T2

ON (T2.TransactionID<=T1.TransactionID

OR T2.TransactionDate<>T1.TransactionDate)

AND (T2.TransactionDate<=T1.TransactionDate)

AND (T2.CustomerID=T1.CustomerID)

GROUP BY T1.CustomerID, T1.TransactionDate,

T1.TransactionAmount, T1.TransactionID;

For an updatable query the same can be done by calling the VBA DCount function:

SELECT DCOUNT("*","Transactions","CustomerID = " & CustomerID &

" AND TransactionDate  <=  #" & FORMAT(TransactionDate,"yyyy-mm-dd") &

"# AND (TransactionID <= " & TransactionID &

" OR TransactionDate <> #" & FORMAT(TransactionDate,"yyyy-mm-dd") & "#)") AS RowNumber, CustomerID, TransactionDate, TransactionAmount

FROM Transactions

ORDER BY CustomerID, TransactionDate, TransactionID;

These queries start a new sequence per CustomerID and numbers the rows in transaction date order.  Note how the primary key TransactionID column is brought into play in each case as the tie-breaker in the event of two or more transactions occurring on the same date.

For future reference, please do not piggy back a new question on an old thread.  Start a new thread, and include a hyperlink to the earlier thread if you wish to refer to it.