Ranking Customers based on Total Sales

[ChuckW]

Hi,
I am trying to rank customers based on sales. I created the following query:

------------------------------------------------------------------------------------
SELECT Q.CUSTOMERNAME, Q.TotalSales, Count(Q2.TOTALSales)+1 AS Rank
FROM [CW qry2008SALES_TOP25a] AS Q left JOIN [CW qry2008SALES_TOP25a] AS Q2
ON (Q.CUSTOMERNAME = Q2.CUSTOMERNAME) and (Q.TotalSales = Q2.TotalSales)
GROUP BY Q.CUSTOMERNAME, Q.TotalSales
-----------------------------------------------------------------------------------------
The problem is that the value for the field "Rank" is 2 for all records. I
wanted 1,2,3 etc. Can someone tell me what is wrong with my query?

Thanks,

Chuck

 

[John Spencer]

SELECT Q.CustomerName
, Q.TotalSales,
1+DCOUNT("*","[CW qry2008SALES_TOP25a]","TotalSales>" & Q.TotalSales) as
Rank
FROM [CW qry2008SALES_TOP25a] AS Q


Perhaps what you want is the following

SELECT Q.CUSTOMERNAME
, Q.TotalSales
, Count(Q2.TOTALSales)+1 AS Rank
FROM [CW qry2008SALES_TOP25a] AS Q LEFT JOIN
[CW qry2008SALES_TOP25a] AS Q2
ON Q.TotalSales > Q2.TotalSales
GROUP BY Q.CUSTOMERNAME, Q.TotalSales


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Allen Browne]

See:
Ranking or numbering records
at:
http://allenbrowne.com/ranking.html#query