Results 1 to 2 of 2
2003-05-29, 16:33 #1
- Join Date
- Feb 2001
- Youngstown, Ohio, USA
- Thanked 0 Times in 0 Posts
Report grouping, record selection (2000 SR1)
I have monthly sales data that I want to create reports on, selecting the top- and bottom-sales customers. On a company-wide basis I have gotten the queries to select the top 50 records and generate nice reports, but for the next step I want to be able to select the top 20 records for each sales division. Can this be accomplished with a single query?
One alternative that I am considering would be to use VBA to loop through each location number, select the desired records, append them to a holding table, and then create my report from all of the tabled data. Would this be the more practical method?
2003-05-29, 16:43 #2
- Join Date
- Mar 2002
- Thanked 30 Times in 30 Posts
Re: Report grouping, record selection (2000 SR1)
You could create a report based on a query that returns the sales divisions, and use a subreport based on a top 20 query that returns the top 20 records for each sales division. See ACC2000: How to Create a Top Values Per Group Report. The MSKB article contains a link to download a sample database that illustrates the technique.