Top 10 Report

[Dustin]

I have a query that is set up to report sales by account, with groupings by
item/sku. For example - -
Account #1 (query groups by)
Item A $100,000 (query groups by - multiple data points sum to this
value)
Item B $50,000
Account Total $150,000

etc..

Since the database I'm working with has 100's of accounts, I am trying to
create a report that shows only the top 10 accounts with the detail shown
above. However, in the query design screen, when selecting '10' from the
dropdown, the report generates only the top 10 'item' groupings rather than
the account totals. Is there anyway to set up this query to pull the top 10
accounts while still grouping the sales totals by item?

thanks in advance -

 

[Allen Browne]

The simplest solution might be to set up a report that shows only the
groupings (item/sku), with a subreport to show the top 10 accounts for the
group. This way you can use TOP 10 in the query, and because it's linked to
the item in the main report, it shows the appropriate top 10.

It is possible to use a subquery to get the top N per group:
http://allenbrowne.com/subquery-01.html#TopN
But that doesn't always work out well in a report.
(It may generate a 'multi-level group-by not allowed' error.)

 

[John Spencer]

You might use a subquery to get the accounts. Generically, that might look
something like the following.

SELECT *
FROM [YourCurrentQuery]
WHERE AccountID in (
SELECT Top 10 AccountID
FROM SomeTable
GROUP BY AccountID
ORDER BY Sum(Amount) Desc)



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