How Do I Make a Rank Query wih Multiple Criteria?

[BarbQb]

Hi All,


I am working on a ranking Items in a query and I am stuck when I need to take more fields into account for the rank.


Currently I have a query that ranks [Location] by the [Date] they were entered for each [Item]. We have multiple locations for each item so the earliest date gets ranked 1 for each [Item]


The SQL below works pretty well so far:

SELECT a.Company, a.Item, a.Location, a.Status, a.Date, Count(*) AS Rank
FROM (SELECT Company, Item, Location, Status, Date 
FROM qrySKULocation02 
GROUP BY Company, Item, Location, Status, Date
)  AS a INNER JOIN (SELECT Company, Item, Location, Status, Date 
FROM qrySKULocation02 
GROUP BY Company, Item, Location, Status, Date
)  AS b ON (a.Item = b.Item) AND (a.Date >= b.Date)
GROUP BY a.Company, a.Item, a.Location, a.Status, a.Date
ORDER BY a.Company, a.Item, a.Date, Count(*);
 

But I need to expand the query and I would like to assign a rank number for each Item taking [Status] into account first, and then by [Date], and then by [Location]


Hopefully that was clear enough and someone can help me out.

Thanks in advance.

[zmbd]

I don't rank very often, and rarely on anything other than one field.

However, from what I remember you will need a series of union queries to tie all of your fields into one, and then do the ranking query against this new union.
just a general tip on ranking Allen Browne's Ranking Tutorial

Personally, when I end up having to create a ranking query, it's usually, but not always (IMHO), because I've not normalized the database properly; thus, it's worth a look to see if you can normalize the table structure a bit more!