Top 5 results in a query

J

Jimbo Razza

Board Regular
Joined
May 21, 2005
Messages
114
Hi There

Is there some way to create a query that will list the top 5 values of a certain feild and have them grouped by another feild.

For example I have the follow feilds in a table:

Employee Name
Employees Manager
Sales For Month.

I need to see the top 5 sales for the month for each Manager.
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Sort by date Sort by votes
You need to do this in a report.

Sort and group on Manager
Sort Descending on Sales
Limit the Detail section to 5 records.

Steps 1 and 2 are straightforward. Step 3, I need to check some references. It's possible to do but I can't put my finger on the answer at the moment.

Denis
 
Upvote 0
Check out this KB article:

ACC2000: How to Create a "Top N Values Per Group" Query
http://support.microsoft.com/kb/210039

SUMMARY
This article shows you two methods that you can use to create queries that list only the top n items per group. By using either one of these methods, you can create a query that displays only the top five salespeople for each region.

HTH,
CT
 
Upvote 0
You need to do this in a report.

Sort and group on Manager
Sort Descending on Sales
Limit the Detail section to 5 records.

Steps 1 and 2 are straightforward. Step 3, I need to check some references. It's possible to do but I can't put my finger on the answer at the moment.
Click to expand...

Would anyone have an insight into this method.

I followed the link supplied by CT Witter (Thanks dude), however I don't understand what I'm reading in it enough to make sense of it.
 
Upvote 0
CT's method will be quicker. I couldn't find the reference last night (I have seen it before...).

What is your table name? We can provide the SQL.

Denis
 
Upvote 0
Table name is "Extract-Route Times".

The grouping feild in called "Area Manager"

The feild I need limited it called "Time Taken"

(I know the above differs from my original example)

I it possible for you to highlight the areas that need to be altered as I need to achieve the same result on multiple tables with varying feilds.

Are you looking at Method 2? If so will I have to create a module for each query I need to set up?
 
Upvote 0
Jimbo, use Method 1. Here's how to do it:

1. You need a query with 2 tables. The table you have already, and the Managers table.

2. Take this SQL code and dump it into Notepad. You can use the Replace function to change table and field names as required. Changes to make:
Code: 
ORIGINAL            CHANGE TO
[Products]          [Extract-Route Times]
[Categories]        [Your Managers table]
[CategoryName]      [Managers field in Managers table]
[CategoryID]        [ManagerID]
[UnitsInStock]      [Time Taken]

If you want bottom 5, not top five, 
 DESC               "" (That's SPACE and DESC)

SQL:
Code: 
SELECT [Categories].[CategoryName], [Products].[ProductName], [Products].[UnitsInStock]
FROM [Categories] INNER JOIN [Products] ON [Categories].[CategoryID] = [Products].[CategoryID]
WHERE ((([Products].[UnitsInStock]) In (Select Top 3 [UnitsInStock] From [Products] Where [CategoryID]=[Categories].[CategoryID] Order By [UnitsInStock] DESC)))
ORDER BY [Categories].[CategoryName], [Products].[UnitsInStock] DESC;

To create query:
New query, Design view.
Dismiss the tables dialog.
Click the SQL option (top left) and paste the SQL code.
Run the query

Denis
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Solved
Struggling with Editting a "search" query
2 3
Replies
22
Views
1K
welshgasman
welshgasman
T
  • Solved
Query to choose 5 records at random while excluding records based on criteria.
Replies
3
Views
298
Micron
M
K
  • Solved
Automaticaly Sum multiple fields in a form
Replies
9
Views
543
welshgasman
welshgasman
K
  • Question
VBA for Check box Select then sends target field data in form to another table
Replies
1
Views
489
welshgasman
welshgasman
N
  • Solved
MS Query and Access Union Query
2 3
Replies
28
Views
1K
mdmilner
M

Forum statistics

Threads
1,215,514
Messages
6,125,265
Members
449,219
Latest member
daynle

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top