Access Query Sequential Numbering (After a sort)

I have a simple query that summarizes sales by region and sorts the results in decending order.

I end up with a list of sales regions greatest to smallest (with counts of stores).

What I then need to do is make a new table that ranks these regions 1 to whatever, highest volume to lowest.
SELECT tdataStores.StrDMACity AS DMA, Sum([TblFY07-VolAndPen].FY07Vol) AS FY07_Vol, Count(tdataStores.StrID) AS Stores
FROM tdataStores INNER JOIN [TblFY07-VolAndPen] ON tdataStores.StrID = [TblFY07-VolAndPen].StrId
GROUP BY tdataStores.StrDMACity
ORDER BY Sum([TblFY07-VolAndPen].FY07Vol) DESC;

Open in new window

kb1esxAsked:
Who is Participating?
 
jmoss111Commented:
You could build a function
declare var
Dim Cntr as Integer
set value
Cntr = 1

Function VolCntr(x)
VolContr = Cntr
Cntr = Cntr + 1
End Function

A make table query

SELECT VolCntr ([FY07Vol]) AS Rank, etc, etc INTO MyRankTable FROM TableInYourExample
0
 
Rey Obrero (Capricorn1)Commented:
see this link
How to Rank Records Within a Query
http://support.microsoft.com/?kbid=208946
0
 
jmoss111Commented:
VolCntr = Cntr
0
 
kb1esxAuthor Commented:
What was the second post for jmoss111?
0
 
jmoss111Commented:
Statement under Function was misspelled s/b VolCntr was VolContr
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.