By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,847 Members | 2,153 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,847 IT Pros & Developers. It's quick & easy.

MS Access Ranking (sum)

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


Code:
Expand|Select|Wrap|Line Numbers
  1. SELECT Sales.CategoryID, Sales.Gross, (Select Count(*) from qrySales Where [Gross] >[Sales].[Gross])+1 AS Ranking
  2. FROM qrySales AS Sales
  3. 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?
Dec 19 '07 #1
Share this Question
Share on Google+
1 Reply


puppydogbuddy
Expert 100+
P: 1,923
Helo, i use the code below to rank my table base on Gross (highest to lowest)


Code:
Expand|Select|Wrap|Line Numbers
  1. SELECT Sales.CategoryID, Sales.Gross, (Select Count(*) from qrySales Where [Gross] >[Sales].[Gross])+1 AS Ranking
  2. FROM qrySales AS Sales
  3. 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):
Expand|Select|Wrap|Line Numbers
  1. Select CustomerID, CustomerName, Sum(Gross) As GrossSales,
  2. (Select Count(*) from qrySales Where GrossSales > GrossSales)+1 AS Ranking
  3. From qrySales
  4. Where SaleDate Between #1/1/2007# And #1/31/2007#
  5. Group By CustomerID, CustomerName
  6. Order By Ranking
Dec 19 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.