MS-Access Multiple Rank Order

[BillChicago]

I'm trying to build a table that has multiple rank orders. I have devised a plan to do a single rank order:

Rank: (Select Count([_Summary Data].EIN) From [_Summary Data] AS Duplicate WHERE Duplicate.[JUL POINTS] > [_Summary Data].[JUL POINTS]+1

Which can be embedded directly into a query. However, I want to try and build this multiple times. Here is a sample data set:

Pts Region
1234 1
1351 2
2042 1
2582 2

And the ideal output I would like to get would be:

Pts Region OverallRank RegionRank
1234 1 4 2
1351 2 3 2
2042 1 2 1
2582 2 1 1

Any help anyone can provide would be greatly appreciated. Thanks!

 

[PHV]

Provided your posted overall rank works:
RegionRank: (Select Count(EIN) From [_Summary Data] AS Duplicate WHERE Duplicate.[JUL POINTS] > [_Summary Data].[JUL POINTS] AND Duplicate.Region = [_Summary Data].Region)+1



Hope This Helps, PH.
FAQ219-2884
FAQ181-2886