Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2003
    Posts
    10

    Unanswered: Is there a way to do this in a query?

    Here is a demo situation of what I'm trying to do

    You have a Customer Field, Date Field, and Product Field

    I have the query set up to select only recordes within a selected date range. Now I want to create a field which tells what product was most common out of the records the query came back with. I've sat here and sat here and just cannot think of a way to do this.

    Any help would be greatly appritiated!

  2. #2
    Join Date
    Dec 2003
    Posts
    172
    I show the product name (group-by) and the total# by product (count) in a query, and then sort descending by the count (so the highest count of any one product is at the top). Then I just set the property of the query to return only 1 record.

    SELECT TOP 1 tblOrders.ProductName, Count(tblOrders.ProductName) AS TotalByProduct
    FROM tblOrders
    GROUP BY tblOrders.ProductName
    ORDER BY Count(tblOrders.ProductName) DESC;

    I'm sure there are other ways (maybe better) to do this.

    JoeG

  3. #3
    Join Date
    Sep 2003
    Posts
    10
    Hey man, can't thank you enough for the help. I'm gonna give it a shot now!

  4. #4
    Join Date
    Sep 2003
    Posts
    10
    Its work'n like a charm, thanks for the advice!

  5. #5
    Join Date
    Dec 2003
    Posts
    172
    Glad to help.

    JoeG

Posting Permissions

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