[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 611
  • Last Modified:

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

0
kb1esx
Asked:
kb1esx
  • 3
1 Solution
 
Rey Obrero (Capricorn1)Commented:
see this link
How to Rank Records Within a Query
http://support.microsoft.com/?kbid=208946
0
 
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
 
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

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now