Results 1 to 2 of 2
  1. #1
    5 Star Lounger
    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?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 28 Times in 28 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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts