MS Access equivalent for using dense_rank in select

Question

In MS Access, I have a table with 2 million account records/rows with various columns of data. I wish to apply a sequence number to every account record. (i.e.- 1 for the first account record ABC111, 2 for the second account record DEF222..., etc.)

Then, I would like to assign a batch number sequence for every 5 distinct account number. (i.e - record 1 with account number ABC111 being associated with batch number 101, record 2 with account number DEF222 being associated with batch number of 101)

This is how I would do it with a sql server query:

select distinct(p.accountnumber),FLOOR(((50 + dense_rank() over(order by        
p.accountnumber)) - 1)/5) + 100 As BATCH from 
db2inst1.account_table p

Raw Data:

AccountNumber    
ABC111           
DEF222           
GHI333           
JKL444           
MNO555           
PQR666           
STU777          

Resulting Data:

RecordNumber    AccountNumber     BatchNumber
1               ABC111            101
2               DEF222            101
3               GHI333            101
4               JKL444            101 
5               MNO555            101 
6               PQR666            102 
7               STU777            102

I tried to make a query that uses SELECT as well as DENSE_RANK but I couldn't figure out how to make it work.

Thanks for reading my question

Answer 1

Something like this would probably work.

I'd first create a temporary table to hold the distinct account numbers, then I'd do an update query to assign the ranking.

CREATE TABLE tmpAccountRank
    (AccountNumber TEXT(10)
        CONSTRAINT PrimaryKey PRIMARY KEY,
    AccountRank INTEGER NULL);

Then I'd use this table to generate the account ranking.

DELETE FROM tmpAccountRank;
INSERT INTO tmpAccountRank(AccountNumber)
SELECT DISTINCT AccountNumber FROM db2inst1.account_table;
UPDATE tmpAccountRank
SET AccountRank =
    DCOUNT('AccountNumber', 'tmpAccountRank',
        'AccountNumber < ''' + AccountNumber + '''') \ 5 + 101

I use DCOUNT and integer division (\ 5) to generate the ranking. This probably will have terrible performance but I think it's the way you would do it in MS Access.

If you want to skip the temp table, you can do it all in a nested subquery, but I don't think it's a great practice to do too much in a single query, especially in MS Access.

SELECT AccountNumber, (SELECT COUNT(*) FROM (SELECT DISTINCT AccountNumber FROM db2inst1.account_table WHERE AccountNumber < t.AccountNumber) q)) \ 5 + 101 FROM db2inst1.account_table t

Actually, this won't work in MS Access; apparently you can't reference tables outside of multiple levels of nesting in a subquery.