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

Hi Ken,

I couldn't open your file, but I used your first sql statement and made sense of it and it works perfectly.

Thanks so much for your help!!!!!!

Mike