Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Top values quandry (XP)

    Scenario: A sales journal, out of which I need to cull the top 25 customers for each branch location. Step 1 was to perform a totals query grouping by branch and customer, summing the amount sold. Step 2 is to...and here's the issue.

    Can I "SELECT TOP 25" across multiple fields? I've been able to get the top 25 branch locations, and the top 25 customers, but haven't been able to figure out how to get what I'm actually looking for.
    <font face="Comic Sans MS"><font color=blue>~Shane</font color=blue></font face=comic>

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Top values quandry (XP)

    I think you're going to have to create a calculated field to rank the customers within the branch, with 1 being the highest rank. Then you can filter for customers <=25 and still group by branch and order by branch and then customer rank.
    Charlotte

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Top values quandry (XP)

    Another solution would be to run a series of queries which give you the totals by customer for a given branch and do top 25 on that. You could then put that into a UNION query which gather all of the top 25s into a single result (or you just paste into a table or Excel). It also seems like you should be able to something using a sub-query, but I haven't been able to figure it out. In any event if you have to do this regularly, the approach Charlotte proposes is probably the best.
    Wendell

Posting Permissions

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