By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,476 Members | 1,529 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,476 IT Pros & Developers. It's quick & easy.

Rank in Sequential Order - MS Access 2003

P: 2
Hi all,
I am a just starting to build my own formula's and limited skills w/ writing sql.

I am having some issues trying to rank by an id and based on the rate of a particular procedure, we reimburse the top 3 highest charge_amt in the following levels... i.e. highest procedure 1 is payed @ 100%, procedure 2 @ 50% and procedure 3 @ 25%.

Unfortunately my data is not in sequential order by charge_amt, so I was trying to run a query only pulling the ID and sorted Charge_amt Desc order, now I need to rank the top 3, then multiply according to the procedure #. I tried count function, but I am obviously missing a step along the way.

Any tips and/or help would be greatly appreciated!

Thanks,
Shana
May 19 '08 #1
Share this Question
Share on Google+
3 Replies


NeoPa
Expert Mod 15k+
P: 31,186
This will be hard but not impossible to do in a single set of SQL using subqueries.
Much easier to save the subquery as a QueryDef (saved query).

Start off by creating a QueryDef in Access called [qryTop3] with the following SQL.
Expand|Select|Wrap|Line Numbers
  1. SELECT TOP 3 [ID],
  2.              [Charge_Amt]
  3. FROM [YourTable]
  4. ORDER BY [Charge_Amt] DESC
With that query now generally available the following SQL should do the trick for you :
Expand|Select|Wrap|Line Numbers
  1. SELECT [ID],
  2.        [Charge_Amt],
  3.        (SELECT Count (*)
  4.         FROM [qryTop3] AS [iTop3]
  5.         WHERE iTop3.Charge_Amt>oTop3.Charge_Amt) AS [Ordinal],
  6.        [Charge_Amt]*(4-IIf([Ordinal]=0,0,[Ordinal]+1))/4 AS [Reimburse]
  7. FROM [qryTop3] AS [oTop3]
  8. ORDER BY [Charge_Amt] DESC
Clearly you can use whatever names suit you best for your queries, and I suspect you'll need to rename [YourTable] too.
May 19 '08 #2

P: 2
Hi Neopa,
Thanks for your reply, although I apologize. I wasn't very clear on my results I was needing. When I ran your first query all it pulled was the top 3 for my whole data set, but I actually the top 3 per id. I tried to manipulate the code, but could get it to pull the top 3 for each id. Your assistance is greatly appreciated:)

Example:
abc $400
abc $750
abc $250
xyz $550
xyz $750
xyz $800

What I need is:
abc $750 1 - will mutliply by 1
abc $400 2 - will mutilply by .5
abc $250 3 - will muptiply by .25
xyz $800 1 - will mutliply by 1
xyz $750 2 - will multiply by 2
xyz $550 3 - will multiply by 3
May 20 '08 #3

NeoPa
Expert Mod 15k+
P: 31,186
Why don't you start by posting what you DID try?
May 20 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.