Top Ten

[1starr]

I need to create a quert that gives me the top 10 most sold item in each store. In other words I have approximately 8 stores that and I want only to find out what items are most popular in each location and with this query I will create a report showing by location what was the 10 most sold item in each location.

 

[Tiro Cupidus]

I'm not sure about doing it for all 8 stores in one query, but you might look up Display only the highest or lowest values in the query's results in Access help. It shows how to limit your query to only the top x or top x%.

 

[Pat Hartman]

Try these articles and download the sample databases that they refer to.

ACC: How to Create a Top N Values per Group Query

ACC: How to Create a Top Values Per Group Report

 

[Rich_Lovina]

With Multiple Tables & Criteria Still Can't Get this one

After checking MS Knowledge refs, can't seem to get the SQL right. And have never quite understood the statement RIGHT JOIN.


I've 50k names, want the Top 15 by salary (Fld Poscode228.To) in each Department (Fld Govdoug.Deptcode)
Here's the SQL code (without any reference to the Top 15):

SELECT GOVDOUG.Name_ID AS TenMostHighestPaid, GOVDOUG.F1_ECON30503, GOVDOUG.Deptcode, POSCODE228.To, STRDIV.DVNAME, STRBRANCH.BRNAME, GOVDOUG.Sect1, DEPT228.State
FROM STRDIV RIGHT JOIN (STRBRANCH RIGHT JOIN (POSCODE228 RIGHT JOIN (DEPT228 RIGHT JOIN GOVDOUG ON DEPT228.dept_id = GOVDOUG.Deptcode) ON POSCODE228.Poscode = GOVDOUG.Poscode) ON STRBRANCH.Branchcode = GOVDOUG.Branchcode) ON STRDIV.Divcode = GOVDOUG.Divcode
WHERE (((GOVDOUG.F1_ECON30503)=Yes))
ORDER BY GOVDOUG.Deptcode, POSCODE228.To DESC;

Any help really apprecaited

 

[Jon K]

I have attached a small sample DB (to return Top 3 in each DeptCode) using "Method 1" in the first MS Knowledge Base Article listed by Pat.

I have used a series of two queries. The first query "qryOne" is basically the same as your query, but with the Right Outer Joins changed to Inner Joins. (I don't think you'll need the outer joins. They are used for returning all the records from the one-side in a one-to-many relationship.)

The second query "qryTwo" returns the Top 3 To in each DeptCode from qryOne:-

SELECT a.*
FROM qryOne AS a
WHERE a.To In (Select Top 3 To from qryOne where DeptCode=a.DeptCode order by To desc);

You can open the DB and run qryTwo.


Notes.
Running a subquery (a select query in brackets) may take time if the table is fairly large or the machine is slow.

When there is a tie in a DeptCode, more than 3 records are returned.


(To open the attached DB in Access 2000 or 2002, just choose Convert and save as a new name when the DB is opened for the first time.)

 

[Rich_Lovina]

Thanks greatly...being one of the older guys I forgot that I had a vbcode patch that does the same thing. Am just getting that to run correctly, and will compare your one as well. One way or the other I think I've got it at last.

 

[1starr]

Thanks guys, but I found out that if I create a query for each location using the "Select Top Ten" statement then create a union query I get my results. Again thanks for all your help.

 

[Rich]

What if you add more locations to your db ?

 

[namliam]

you could write some vba to create a top 10 query for every existing store/location.

Question: Can you do Top N on a crosstab query?

Regards

 

[Rich_Lovina]

Thanks your input. I have some code, and still tryin to get that working correctly.