MS Access Ranking (sum)

[arshesander]

Helo, i use the code below to rank my table base on Gross (highest to lowest)


Code:

SELECT Sales.CategoryID, Sales.Gross, (Select Count(*) from qrySales Where [Gross] >[Sales].[Gross])+1 AS Ranking
FROM qrySales AS Sales
ORDER BY Sales.Gross DESC;

But my problem is If I want Rank my Table base on Sum Of Gross per each Customer with a date range of 1/1/2007 to 1/31/2007?

kindly help me what code should i write pls?

[puppydogbuddy]

Helo, i use the code below to rank my table base on Gross (highest to lowest)


Code:

SELECT Sales.CategoryID, Sales.Gross, (Select Count(*) from qrySales Where [Gross] >[Sales].[Gross])+1 AS Ranking
FROM qrySales AS Sales
ORDER BY Sales.Gross DESC;

But my problem is If I want Rank my Table base on Sum Of Gross per each Customer with a date range of 1/1/2007 to 1/31/2007?

kindly help me what code should i write pls?

Try this (replace field names I used with yours):

Select CustomerID, CustomerName, Sum(Gross) As GrossSales,
(Select Count(*) from qrySales Where GrossSales > GrossSales)+1 AS Ranking
From qrySales
Where SaleDate Between #1/1/2007# And #1/31/2007#
Group By CustomerID, CustomerName
Order By Ranking