Rank in Sequential Order - MS Access 2003

[WataMess]

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

[NeoPa]

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.

SELECT TOP 3 [ID],
             [Charge_Amt]
FROM [YourTable]
ORDER BY [Charge_Amt] DESC

With that query now generally available the following SQL should do the trick for you :

SELECT [ID],
       [Charge_Amt],
       (SELECT Count (*)
        FROM [qryTop3] AS [iTop3]
        WHERE iTop3.Charge_Amt>oTop3.Charge_Amt) AS [Ordinal],
       [Charge_Amt]*(4-IIf([Ordinal]=0,0,[Ordinal]+1))/4 AS [Reimburse]
FROM [qryTop3] AS [oTop3]
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.

[WataMess]

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

[NeoPa]

Why don't you start by posting what you DID try?