Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    New Jersey, USA
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    top 20 in a query (97 sr2)

    Good Morning,
    This is driving me crazy hopefully some can help.

    I need to find the top 20 accounts , based on position amount, for each rep. My table contains 15,000 records(accounts) and I have 50 reps. I created a query that is ordered by the rep asending and position descending. This gives me everything except I only need the first 20 records for each rep.

    Each record has 3 fields account, rep, amount

    Thanks
    Cathy

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: top 20 in a query (97 sr2)

    1) Have you created a Totals query with Group By on the rep and Sum on the amount?
    2) Have you tried to set the Top Values property of the query to 20?
    - Open the query in design view.
    - Click in an empty part of the upper half of the query window.
    - Activate the Properties window.
    - Enter 20 in the Top Values property (this value is not available in the dropdown list, but you can type it in).

  3. #3
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    New Jersey, USA
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: top 20 in a query (97 sr2)

    Yes but I only get 20 records back I need 20 for each different rep number

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: top 20 in a query (97 sr2)

    Where do you want to use this? In a form, or in a report, or otherwise?

  5. #5
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Piscataway, New Jersey, USA
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: top 20 in a query (97 sr2)

    (Edited by HansV to make URL clickable - see <!help=19>Help 19<!/help>)

    Try this link:
    http://support.microsoft.com/kb/296449

  6. #6
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    New Jersey, USA
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: top 20 in a query (97 sr2)

    I just want a query that I can export into excel

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: top 20 in a query (97 sr2)

    Try this:
    - Create a query based on your table.
    - Add the rep, account and amount fields.
    - Enter the following expression in the Criteria line for the amount field:

    In (SELECT TOP 20 t.amount FROM <!t>[TableName] AS t WHERE t.rep=<!t>[TableName].rep ORDER BY t.amount DESC)

    - Replace TableName with the name of your table.
    - Order the query ascending on rep, descending on amount.

Posting Permissions

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