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

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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