Top Values within multiple groups

[Don]

I'm not sure this can be done, but I hope so. I have a query with over 6000
records and within that query I have a field that is titled Department. The
query contains records from 300 departments. Here is what I'd like to do:
I'd like to return the top 5 records for sales, from each department.

I know how to get the top record from each, but not the top 5. I know how
to get the top 5 from my query, but not from each department. Can someone
show this road-weary idiot how to do this? Thanks.

 

[KARL DEWEY]

Try this --
SELECT [Department], (SELECT TOP 5 [Sales] FROM [YourTable] AS [XX] WHERE
[XX].[Department] = [YourTable].[Department] ORDER BY [Sales] DESC) AS
Top_Sales
FROM [YourTable]
ORDER BY [Department];

 

[Don]

Mr. Dewey,

Can you tell me what the [XX] is in the code you wrote. I cannot get this
to work. Thanks.
--
Don Rountree

Try this --
SELECT [Department], (SELECT TOP 5 [Sales] FROM [YourTable] AS [XX] WHERE
[XX].[Department] = [YourTable].[Department] ORDER BY [Sales] DESC) AS
Top_Sales
FROM [YourTable]
ORDER BY [Department];

--
Build a little, test a little.

I'm not sure this can be done, but I hope so. I have a query with over 6000
records and within that query I have a field that is titled Department. The
query contains records from 300 departments. Here is what I'd like to do:
I'd like to return the top 5 records for sales, from each department.

I know how to get the top record from each, but not the top 5. I know how
to get the top 5 from my query, but not from each department. Can someone
show this road-weary idiot how to do this? Thanks.

 

[John Spencer]

Take a look at Allen Browne's site
http://allenbrowne.com/subquery-01.html#TopN

That has examples on how to get the top N per group

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[KARL DEWEY]

Can you tell me what the [XX] is in the code you wrote.
XX is an alias for the table. I use the table twice but differently so it
must have a different name.
This does not tell me a whole lot so as to know how to advise you.
What is the results? What are the errors?
Post the SQL from your query. Post sample data.

--
Build a little, test a little.

Mr. Dewey,

Can you tell me what the [XX] is in the code you wrote. I cannot get this
to work. Thanks.
--
Don Rountree

Try this --
SELECT [Department], (SELECT TOP 5 [Sales] FROM [YourTable] AS [XX] WHERE
[XX].[Department] = [YourTable].[Department] ORDER BY [Sales] DESC) AS
Top_Sales
FROM [YourTable]
ORDER BY [Department];

--
Build a little, test a little.

I'm not sure this can be done, but I hope so. I have a query with over 6000
records and within that query I have a field that is titled Department. The
query contains records from 300 departments. Here is what I'd like to do:
I'd like to return the top 5 records for sales, from each department.

I know how to get the top record from each, but not the top 5. I know how
to get the top 5 from my query, but not from each department. Can someone
show this road-weary idiot how to do this? Thanks.

 

[Don]

I apologize for being so thick. If you could make me understand this code,
I'll be able to duplicate its use in the future.

My data is structured as such:

Table = tblGROCERY
Field = Department
Field = Sales
Field = Item Number
Field = Item Description

I'd like to return the top 5 sales results from each Department. If you
could help me once more with the Syntax for my SQL statement, I won't trouble
you again. Thanks.
--
Don Rountree

Can you tell me what the [XX] is in the code you wrote.

XX is an alias for the table. I use the table twice but differently so it
must have a different name.
This does not tell me a whole lot so as to know how to advise you.
What is the results? What are the errors?
Post the SQL from your query. Post sample data.

--
Build a little, test a little.

Mr. Dewey,

Can you tell me what the [XX] is in the code you wrote. I cannot get this
to work. Thanks.
--
Don Rountree

Try this --
SELECT [Department], (SELECT TOP 5 [Sales] FROM [YourTable] AS [XX] WHERE
[XX].[Department] = [YourTable].[Department] ORDER BY [Sales] DESC) AS
Top_Sales
FROM [YourTable]
ORDER BY [Department];

--
Build a little, test a little.


:

I'm not sure this can be done, but I hope so. I have a query with over 6000
records and within that query I have a field that is titled Department. The
query contains records from 300 departments. Here is what I'd like to do:
I'd like to return the top 5 records for sales, from each department.

I know how to get the top record from each, but not the top 5. I know how
to get the top 5 from my query, but not from each department. Can someone
show this road-weary idiot how to do this? Thanks.

 

[John Spencer]

SELECT Department, Sales, [Item Number], [Item Description]
FROM tblGrocery
WHERE Sales in
(SELECT TOP 5 Sales
FROM tblGrocery as TEMP
WHERE Temp.Department = tblGrocery.Department
ORDER BY Sales DESC)
ORDER BY Department, Sales DESC

This query will show any ties for fifth place.

Open a anew query and paste the query in the SQL view.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County