MS Access 2010 Ranking Query comparing two columns

Question

Your gracious help on this problem will be very appreciated for programming noob. I've tried to search google world for last 3 days and no luck.

Background: I'm building a comprehensive analysis of warranty database where I have to compare two columns of values to prioritize in product groups for our team needs to investigate. These are; 1. First by Numbers of Failures 2. When Numbers of Failures are tied, then secondly by, Warranty Cost

Steps Taken: I've used Subquery techniques to get what I want. But using this route was so slow for query to complete. (11,000 rows, literally freeze my comp at least 5 min). Thus I googled and found this user on following thread talks exact, same challenge as mine, luckily got resolved using INNER JOIN technique instead as answered by other expert. Yay!

Please refer to www.pcreview.co.uk/forums/ranking-performance-slow-t2844316.html

Well, except.....my query is very similar to his, but I'd also like to compare 2nd column when failures are tied, thus get unique ranks for all in same group. (Above user only compares one column, and no concerns about ties, unlike me for his/her application)

Desired Output:

Table1.

Part Failures Cost Desired-Rank

A    10      $5     1
A    5       $3     3
A    5       $20    2
B    5       $10    2
B    5       $5     3
B    9       $2     1

My Query so far

SELECT Count(*) AS Rank, dupe.Part, dupe.Failures, dupe.Cost 
FROM [Table1] AS dupe
INNER JOIN [Table1] AS dupe1
ON dupe.Part = dupe1.Part and 
    dupe.Failures <= dupe1.Failures and dupe.Cost<=dupe1.Cost
Group By dupe.Part, dupe.Failures, dupe.Cost;

I tried to tweak the and dupe.Cost<=dupe1.Cost part for last few days without really knowing the basics of programming. (I'm mostly learning through copying others codes and try to understand it after seeing the output)

Please note, I think I have to stick to this JOIN ranking technique, as it extremely cut down query time vs subquery

Your expertise is greatly appreciated!!

Answer 1

If you try to do this strictly using SQL, then your query can get massive as it has to lots of sub queries. A better approach is to use a function. This will only work if your using the query within Access, and not if your just connecting to it.

First, the function (placed into a module):

Function RankMe(Part As String) As Long
   Static LastPart As String
   Static RankNum As Long
   If Part <> LastPart Then
      RankNum = 1
      LastPart = Part
   Else
      RankNum = RankNum + 1
   End If
   RankMe = RankNum
End Function

And you would use it like this:

SELECT Table1.Part, Table1.Failures, Table1.Cost, RankMe([Part]) AS Rank, Table1.[Desired-Rank], *
FROM Table1
ORDER BY Table1.Part, Table1.Failures DESC , Table1.Cost DESC;

FYI, the Static statement means that the variable will keep it's value, and not be reset each time the function is called.