Results 1 to 2 of 2
  1. #1
    ariansman is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2013
    Posts
    57

    how to get a summary query

    I have the following table: John's daily expenses:
    ID Date item cost


    1 01/01/2021 milk 2
    2 01/01/2021 apple 3


    3 01/01/2021 cheese 7


    4 05/01/2021 milk 4
    5 05/01/2021 bread 2
    6 05/01/2021 butter 12


    7 08/01/2021 milk 2
    8 08/01/2021 apple 5
    9 08/01/2021 orange 6


    how can I make a query to get the whole cost in each day, and also the sum of the expenses up to each day like the following?
    Date dailypaid total


    01/01/2021 12 12


    05/01/2021 18 30


    08/01/2021 13 43


    thank you

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you need two queries

    the first is an aggregate query - something like

    Code:
    SELECT myDate, sum(expensevalue) as dailyspend
    from myTable
    GROUP BY myDate

    the second to use an incremental aggregate query based on the first query - might be something like

    Code:
    SELECT A.myDate, A.dailyspend, sum(B.dailyspend) as spendtodate
    FROM firstquery A, firstquery B
    WHERE B.myDate<=A.myDate
    ORDER BY A.myDate
    note Date is a reserved word and should not be used as a field name

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

Similar Threads

  1. Create Summary Query
    By wdyl121 in forum Access
    Replies: 1
    Last Post: 10-31-2016, 03:03 AM
  2. Summary Query in Report
    By chris.williams in forum Queries
    Replies: 3
    Last Post: 07-17-2013, 11:33 AM
  3. Summary query
    By dsk96m in forum Queries
    Replies: 3
    Last Post: 02-29-2012, 01:40 PM
  4. Summary Query - How to?
    By ritch in forum Access
    Replies: 16
    Last Post: 02-17-2010, 03:18 PM
  5. Product in a Summary Query
    By Fletch in forum Queries
    Replies: 0
    Last Post: 12-11-2008, 03:14 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