|  | Top 10 Report | | |
| | | Dustin |  |
|  Posted: Tue Sep 23, 2008 12:34 pm Post subject: Top 10 Report |  |
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 | |
| | Posted: Tue Sep 23, 2008 12:51 pm Post subject: Re: Top 10 Report | |
| |  | |
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:
LINK
But that doesn't always work out well in a report.
(It may generate a 'multi-level group-by not allowed' error.)
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - LINK
Reply to group, rather than allenbrowne at mvps dot org.
"Dustin" <Dustin@discussions.microsoft.com> wrote in message
news:80484A31-B25A-40DE-A790-C1749E87D62E@microsoft.com...
| Quote: | 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? |
|
| |
| | | John Spencer | |
| | Posted: Tue Sep 23, 2008 2:43 pm Post subject: Re: Top 10 Report | |
| | | |
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
Dustin wrote:
| Quote: | 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 -
|
|
| |
|
|
