Results 1 to 3 of 3
  1. #1
    Trooper13 is offline Novice
    Windows 10 Access 2007
    Join Date
    May 2019
    Posts
    2

    Summarize results of a query

    Hello,
    I am trying to create a query that, for a given date range, will count the number of order of a certain color there are. For example, in a given 7 day period (say May 1 to May 7), we might sell 7 blue units, 5 black units, 9 red units, etc.

    Using a Totals line, I am able to Count the number of units sold in the date range, but it also breaks the colors down by individual day in the date rate. So if I sold 2 black units on May 3, 2 black units on May 4, and 1 black unit on May 5, I get individual line items for each date, when I'd really like the color total for the entire date range in one line item.

    My current query is:

    Field: Color Color PurchDate
    Table: Orders Orders Orders
    Total: Group By Count GroupBy


    Sort: Ascending
    Show: Checked Checked Checked
    Criteria: >=[StartDate] And <=[EndDate]

    As you can tell, I'm just getting going in Access.

    Thanks for any pointers.
    Trooper13

  2. #2
    Trooper13 is offline Novice
    Windows 10 Access 2007
    Join Date
    May 2019
    Posts
    2
    Looks like formatting didn't hold so my query columns look jumbled. Sorry, I'll get better over time.

  3. #3
    Minty is online now VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Change you query type to a crosstab, and have a play.

    You should be able to easily get your results the way you want.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

Please reply to this thread with any new information or opinions.

Similar Threads

  1. summarize query results
    By epardo87 in forum Access
    Replies: 1
    Last Post: 10-22-2017, 04:56 PM
  2. Query to summarize data by month
    By wpryan in forum Forms
    Replies: 1
    Last Post: 12-26-2015, 06:44 AM
  3. Query help to summarize data by work week
    By saseymour in forum Queries
    Replies: 4
    Last Post: 07-18-2013, 07:29 AM
  4. Replies: 2
    Last Post: 11-04-2012, 02:08 PM
  5. query to summarize top ranking data
    By CMR in forum Queries
    Replies: 1
    Last Post: 09-17-2012, 02:08 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums