Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Top Entries (2000)

    Hi, I have a simple select query that I wish to modify to show the top 5 entries for a given field. E.g. say top entry is Fish, I wish the query to show all the records for fish, then list the next top record and all the entries for that. How can I modify the query to do this?


    Thanks Darren.

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

    Re: Top Entries (2000)

    I would do this in two steps:

    1. Create a top 5 query:
    - Create a query based on the table.
    - Add the field you want to select on.
    - Add any other field, for instance an ID field.
    - Select View | Totals
    - Leave the Total option for the first field as Group By, set the second to Count.
    - Set the sort order for the second field to Descending.
    - Click in an empty part of the upper half of the query window, then activate the Properties window.
    - Select 5 in the Top property.
    - Save this query as qryTop5.
    - The SQL for this query will look like this (with the appropriate names substituted; this example is for the Orders table in the Northwind database):

    SELECT TOP 5 CustomerID, Count(OrderID) AS CountOfOrderID
    FROM Orders
    GROUP BY CustomerID
    ORDER BY Count(OrderID) DESC;

    2. Create a new query based on the table and on the query you just created.
    - Join them on the field you want to select on.
    - Add * from the table to the query grid.
    - Add the CountOfSomething field from the query.
    - Set the Sort order for the count field to Descending and clear the Show check box.
    - To handle ties, add the field you want to select on to the query grid. It should be to the right of the CountOfSomething field.
    - Set the Sort order for this field to Ascending and clear the Show check box.
    - In the example based on the Orders table in Northwind, the SQL for this query would be

    SELECT Orders.*
    FROM Orders INNER JOIN qryTop5 ON Orders.CustomerID = qryTop5.CustomerID
    ORDER BY qryTop5.CountOfOrderID DESC , Orders.CustomerID;

  3. #3
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Top Entries (2000)

    Thanks Hans

    Darren.

Posting Permissions

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