Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Nov 2003
    Location
    London, Gtr London, England
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Top 10 selection (2000 9.0.4402 SR-1)

    I have a table that has the value of premiums by Broker and by Class of business. I need to be able to pull the Top 10 Brokers by Class and by Value from this table and for the query to group as follows:

    Class

  2. #2
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Top 10 selection (2000 9.0.4402 SR-1)

    Niven,
    See if this is what you need. It uses a subquery to find the top ten (attachment)

    Scott

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

    Re: Top 10 selection (2000 9.0.4402 SR-1)

    See MSKB article ACC2000: How to Create a "Top N Values Per Group" Query. If you would like specific help, could you attach a stripped down copy of your database?

  4. #4
    3 Star Lounger
    Join Date
    Nov 2003
    Location
    London, Gtr London, England
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Top 10 selection (2000 9.0.4402 SR-1)

    Many thanks your reply, thought it was going to do the trick, however when applying it to my data table it produced a mixed bag of results.

    I wonder if you could have a look at the query again with my data in your table (dropped the Class description) please?

    Cheers,

    Niven

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

    Re: Top 10 selection (2000 9.0.4402 SR-1)

    Does this version (based on the table in the database you attached) do what you want?

    SELECT T.Class, T.Broker, T.Sales
    FROM Table1 AS T
    WHERE (((T.Broker) In (Select Top 10 Broker From Table1 Where Class = T.Class Order By Sales Desc)))
    ORDER BY T.Class, T.Sales DESC;

    See attachment.

  6. #6
    3 Star Lounger
    Join Date
    Nov 2003
    Location
    London, Gtr London, England
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Top 10 selection (2000 9.0.4402 SR-1)

    Excellent! This seems do what I want, the first part anyway. I still have to pick up the rest of the brokers and code them as Other, but this has put me well on the way.

    Cheers!

    Niven <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30>

  7. #7
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Top 10 selection (2000 9.0.4402 SR-1)

    Niven,
    My suggestion didn't work because you don't have at least 10 sales for each class.
    I created a function from the link references in Hans post.
    This is a better solution because the number of classws and sales might change.

    Scott

  8. #8
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Top 10 selection (2000 9.0.4402 SR-1)

    Hans,
    I bow to you O Great one. <img src=/S/surrender.gif border=0 alt=surrender width=31 height=23>

Posting Permissions

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