Convert rank over partition by SQL code to Microsoft access

Question

0.00/5 (No votes)

See more:

I am trying to get access to export a report with pricing for product numbers and customers, but our SQL tables only have this information in our order tables which means I need to rank the information and then only pick the information with the rank of 1. I have the rank code for SQL below, but I just found out that Access doesn't accept rank over partition by. If anyone has any ideas on how I would convert the below SQL code to being acceptable to Access I would appreciate it.

SQL

 select
  rank() over (partition by cusnum, prodnum order by MAX(Order_Line.upddate) DESC) as 'RANK',
  bilcusnum,
  prodnum,
  soldby_price
from Orders
join Order_Line on Orders.ordnum = Order_Line.ordnum
group by prodnum, cusnum, soldby_price, Order_Line.upddate;

What I have tried:

I have tried the above code, but I can't figure out how to convert it.

Posted 13-Jul-22 10:56am

Member 15705757

Updated 13-Jul-22 23:15pm

Add a Solution

2 solutions

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Dave Kreskowiak · Answer 1 · 2022-07-13T12:40:00

Solution 1

There is no equivalent to Rank() in Access. There isn't even a close work-around either.

Access is limited to the SQL syntax of ANSI'92. Rank wasn't introduced until ANSI'99.

Posted 13-Jul-22 12:40pm

Dave Kreskowiak

Comments

Member 15705757 14-Jul-22 9:10am

So there is no way to convert the above code into something I can use in SQLServer/Access?

Dave Kreskowiak 14-Jul-22 9:34am

I didn't say that. It's entirely possible to write your own Rank function in Access, but don't ask me for examples. I don't use Access because it's a desktop database app, not something I would use for any production work.

CHill60 · Answer 2 · 2022-07-13T23:15:00

Solution 2

There are some solutions listed here. Use Access SQL to do a grouped ranking[^]

Posted 13-Jul-22 23:15pm

CHill60

Convert rank over partition by SQL code to Microsoft access

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Convert rank over partition by SQL code to Microsoft access

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us