Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Top Values per Group (2003)

    I am trying to adapt some query code from a query that uses the Northwind Trader database. The Northwind Trader data includes 2 tables and the criteria code for the field UnitsinStock is as follows:
    Criteria: In(Select Top 3 [UnitsInStock] From Products Where [CategoryID]=[Categories].[Categoryid] Order By [UnitsInStock] Desc).

    I only have one table that contains the field Contract (that would replace [UnitsInStock] and the County that I want to group by to see the top 3 Clients (Contracts). How would I adjust the Criteria for the Contract field. The County field has multiple Clients that have Contracts. I guess the Where clause is what is stumping me. I hope this makes sense. Thanks for any help you can provide.

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

    Re: Top Values per Group (2003)

    What is Contract? A currency field?

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Top Values per Group (2003)

    No. It is a number field. A client can have say 200 contracts. A county can have numerous clients. Each client has a number of contracts. I want to get the top 3 clients (contract wise) for each county.

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

    Re: Top Values per Group (2003)

    This is different from the example you mentioned. There, you were interested in the top values of the UnitsInStock field. Here, you are not interested in the values of the Contract field at all, but in the number of records per client. So you must first create a totals query that calculates the number of contracts per county and per client. You must substitute the actual table name and field names. If one of the names contains spaces, enclose it in square brackets, e.g. <!t>[Table Name]<!/t>.

    SELECT County, Client, Count(*) AS NbrOfContracts
    FROM TableName
    GROUP BY County, Client
    ORDER BY Count(*) DESC

    Save this query as qryNbrOfClients. Next, create a new query:

    SELECT County, Client, NbrOfClients
    FROM qryNbrOfClients
    WHERE Client IN (SELECT TOP 3 q.NbrOfClients FROM qryNbrOfClients AS q WHERE q.NbrOfClients=qryNbrOfClients.NbrOfClients)
    ORDER BY County, NbrOfClients DESC

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Top Values per Group (2003)

    The query I have is as follows:
    State, County, Client number, Client Name, Contracts.
    I already have it totaled

    The results I am looking for are
    State County Client Number Client Name Contracts
    PA Armstong 0001 client A 50
    PA Armstong 0002 Client B 40
    PA Armstong 0003 Client C 30

    CA Butler 0004 Client D 10
    CA Butler 0005 Client E 9
    CA Butler 0006 Client F 8

    and so on

    I want the 3 top Clients (as to Contracts) for each County
    I thought I could just adapt the same logic as the example I found except I have 1 table and not 2. My grouping field is in the same table so I don't have a where clause to join the 2 tables. When I take out the Where clause, I just get the top 3 clients. I don't get the top 3 for each county.

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

    Re: Top Values per Group (2003)

    Aha, I was misled by your mention of a field Contract in the first post, so I thought it was an ID field instead of a count.

    Say the query you already have (the one with State, County, Client number, Client Name, Contracts) is named Query1. Sort this query descending on Contracts. If you don't want to modify the query itself, create a new query based on your query, add all fields, and sort it descending on Contracts. Use the new query instead of Query1 in the following.

    Create a query with the following SQL:

    SELECT [State], [County], [Client Number], [Client Name], [Contracts]
    FROM [Query1]
    WHERE [Client Number] In (SELECT Top 3 q.[Client Number] FROM [Query1] AS q WHERE q.[County]=[Query1].[County])
    ORDER BY [County], [Contracts] DESC

Posting Permissions

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