[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Ranking per Group in MS Access Query

Posted on 2012-09-07
8
Medium Priority
?
2,889 Views
Last Modified: 2012-09-17
Hi all

I am hoping that someone would be able to provide some quick assistance on this one.

I have a MS Access database that stores all of the sales data/transactions by customer name in each market.  The fields in this table are:

Market_ID
Customer_No
Customer_Name
Volume_Sales


I also have a query that sums the volume sales for each customer called qry_Customer_Total_Sales.


I created another query that allowed me to rank each customer in the database regardless of which market they are from (i.e. across the entire business) as follows:

SELECT Ranking.Market_ID, Ranking.Customer_No, Ranking.Customer_Name, Ranking.Total, (Select Count(*) from qry_Customer_Total_Sales Where [Total] >   [Ranking].[Total])+1 AS Ranking
FROM qry_Customer_Sales AS Ranking
ORDER BY Ranking.Market_ID, Ranking.Total DESC;

However, I would like to now rank each customer within their respective markets.  For example, I would like to show that a customer may be ranked #20 across all customers but ranked #1 in their respective market.

I suspect that a subquery would be needed (similarly to the Top N per group query) but I am having some trouble figuring this out.

Can someone please assist?

Thanks!
0
Comment
Question by:staceymoore
  • 4
  • 2
  • 2
8 Comments
 
LVL 93

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 1000 total points
ID: 38378010
The following seems to work for me:

SELECT x.Market_ID, x.Customer_No, x.Customer_Name, x.Total, Count(y.Total) + 1 AS Rank
FROM
    (SELECT s1.Market_ID, s1.Customer_No, s1.Customer_Name, Sum(s1.Volume_Sales) AS Total
    FROM SalesTable s1
    GROUP BY s1.Market_ID, s1.Customer_No, s1.Customer_Name) x LEFT JOIN
    (SELECT s2.Market_ID, s2.Customer_No, s2.Customer_Name, Sum(s2.Volume_Sales) AS Total
    FROM SalesTable s2
    GROUP BY s2.Market_ID, s2.Customer_No, s2.Customer_Name) y ON x.Total < y.Total And x.Market_ID = y.Market_ID
GROUP BY x.Market_ID, x.Customer_No, x.Customer_Name, x.Total
ORDER BY x.Market_ID, x.Total Desc

Open in new window

Q-27857430.mdb
0
 
LVL 31

Expert Comment

by:hnasr
ID: 38378186
What about if the total value repeats? Use the same rank?
0
 

Author Comment

by:staceymoore
ID: 38396753
Hi matthewspatrick

Your suggestion works great but sometimes I get this error:

screenshot
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 31

Accepted Solution

by:
hnasr earned 1000 total points
ID: 38397438
In design mode On phrase works with =, for inequality use Where clause.

Try: (Assisted by matthewspatrick's comment)
SELECT x.Market_ID, x.Customer_No, x.Customer_Name, x.Total, Count(y.Total)   AS Rank
FROM (SELECT s1.Market_ID, s1.Customer_No, s1.Customer_Name, Sum(s1.Volume_Sales) AS Total FROM SalesTable AS s1 GROUP BY s1.Market_ID, s1.Customer_No, s1.Customer_Name)  AS x LEFT JOIN (SELECT s2.Market_ID, s2.Customer_No, s2.Customer_Name, Sum(s2.Volume_Sales) AS Total FROM SalesTable AS s2 GROUP BY s2.Market_ID, s2.Customer_No, s2.Customer_Name)  AS y ON (x.Market_ID = y.Market_ID)
Where  x.Total <= y.Total 
GROUP BY x.Market_ID, x.Customer_No, x.Customer_Name, x.Total
ORDER BY x.Market_ID, x.Total DESC;

Open in new window


Note: Query fails to rank, as 1, 2, 3, ...,  if some sales are equal. Try by changing all sales to 1.
0
 

Author Comment

by:staceymoore
ID: 38398594
hnasr

Thanks for your solution!  It works quite well.

In this case, I am not too concerned about the ties since I am only focusing on the top 20.
0
 
LVL 31

Expert Comment

by:hnasr
ID: 38399553
Welcome!
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 38405842
staceymoore,

That is NOT an error.  It is a message.

The Jet/ACE database engine that Access uses behind the scenes is perfectly happy to use a join condition that is not a simple equi-join.  However, Access's GUI query design view can only show diagrams of queries that use equi-joins and/or cross (or Cartesian) joins.

That message is simply telling you that the query cannot be shown in the normal design view.

Patrick
0
 
LVL 31

Expert Comment

by:hnasr
ID: 38408148
Clarification:
CROSS JOIN Does not use ON phrase. It is just as: A data set composed of the data set of one table repeated for every record of the data set of the other table.

In access:
Select * from tableName as t1, tablename2 as t2;

In MS SQL:
Select * from tableName as t1, tablename2 as t2;
Select * from tableName as t1 CROSS JOIN tablename2 as t2;
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question