asked on

Rank Query with Ties in MS Access

I have an Access ranking query that is grouping results for a particular account. That part seems to be working fine but I am getting ties when the Denial_Adj_Amount is the same.

Here's my query:

SELECT [835Denials].RRC, [835Denials].REGION, [835Denials].FACILITY, [835Denials].MBO, [835Denials].FACILITY_STATE, [835Denials].FACILITY_CODE, [835Denials].MASTER_ID, [835Denials].ACCT_NUMBER, [835Denials].PT_TYPE, [835Denials].SumOfADJ_AMOUNT AS Denial_Adj_Amount, [835Denials].REASON_CODE, [835Denials].REMARK_CODE,

(select count(*) from [835Denials] AS RANK where [835Denials].Master_ID=RANK.Master_ID AND [835Denials].SumOfADJ_AMOUNT<=RANK.SumOfADJ_AMOUNT
GROUP BY [835Denials].MASTER_ID,[835Denials].REASON_CODE, [835Denials].REMARK_CODE
) AS RANK
FROM 835Denials;

What do I need to do so there won’t be any ties? And when there is a tie to then adjust the ranking by Reason_Code in ASC order & if need be, by Remark_Code. Please help!

Rey Obrero (Capricorn1)

try, revising the count portion with this

(select count(*) from [835Denials] AS RANK where [835Denials].Master_ID=RANK.Master_ID AND [835Denials].SumOfADJ_AMOUNT<=RANK.SumOfADJ_AMOUNT And [835Denials].[Reason_code] < RANK.[Reason Code]
GROUP BY [835Denials].MASTER_ID,[835Denials].REASON_CODE, [835Denials].REMARK_CODE, [835Denials].[Reason_code]
Order BY [835Denials].[Reason_code]
) AS RANK

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

KCREN12

ASKER

That worked but what do I do when the Reason Code is the same so then it needs to look at Remark_Code.I tried this but the other ranking disappeared.

(select count(*) from [835Denials] AS RANK where [835Denials].Master_ID=RANK.Master_ID AND [835Denials].SumOfADJ_AMOUNT<=RANK.SumOfADJ_AMOUNT AND [835Denials].[Reason_code] <= RANK.[Reason_Code]
AND [835Denials].[Remark_code] <= RANK.[Remark_Code]