What is the equivalaent syntax for DENSE_RANK over for MS Access?

hatic
hatic used Ask the Experts™
on
how can I write this in access?
SELECT dense_rank() over(partition by field1, field 2 order by field 3) as name
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
You can't because MS ACCESS don't have partioned tables feature.
Most Valuable Expert 2012
Top Expert 2008

Commented:
Are you looking for this?

ACC2000: How to Rank Records Within a Query
http://support.microsoft.com/kb/208946
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
As mentioned earlier, dense_rank() function is not available in Access.

RANK() equivalent can be achieved as given below:

http://www.dbforums.com/microsoft-access/1637016-rank-records-access-sql-row-number.html

But DENSE_RANK() is not available..
Commented:
thanks for your helps
I couldn't find a very to do it in sql
I wrote dense rank and row number functions in vba, copied my table to a recordset and find rankings with the functions and copy them back to a table
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
>> I wrote dense rank and row number functions in vba, copied my table to a recordset and find rankings with the functions and copy them back to a table

If this problem was resolved, you can close out this question right..

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial