how to get a summary querry (1 Viewer)

ariansman

Registered User.
Local time
Yesterday, 20:52
Joined
Apr 3, 2012
Messages
143
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/21 12 12

05/01/2021 18 30

08/01/2021 13 43

thank you
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 20:52
Joined
Oct 29, 2018
Messages
15,227
Hi. Look up Totals query and Running Sum query.

Sent from phone...
 

Isaac

Lifelong Learner
Local time
Yesterday, 20:52
Joined
Mar 14, 2017
Messages
5,148
For the first item, study on Aggregate queries. For the second, search this site on Running Sum in Query
 

arnelgp

error reading drive A:
Local time
Today, 11:52
Joined
May 7, 2009
Messages
12,825
copy and paste on SQL View of Query Designer.
replace yourTable with the Name of your table.
then you study.
Code:
SELECT yourTable.Date,
    Sum(yourTable.Cost) AS Total,
    DSum("Cost","yourTable","[Date] <= #" & Format([yourTable].[Date],"m/d/yyyy") & "#") AS RunningTotal
FROM yourTable
GROUP BY yourTable.Date;
 

ariansman

Registered User.
Local time
Yesterday, 20:52
Joined
Apr 3, 2012
Messages
143
copy and paste on SQL View of Query Designer.
replace yourTable with the Name of your table.
then you study.
Code:
SELECT yourTable.Date,
    Sum(yourTable.Cost) AS Total,
    DSum("Cost","yourTable","[Date] <= #" & Format([yourTable].[Date],"m/d/yyyy") & "#") AS RunningTotal
FROM yourTable
GROUP BY yourTable.Date;
very simple and instructive for me as a beginner. :)
Thank you.
How would it be if we have a number as OrderID instead of the date?
I mean the original table is like
Code:
ID      Date          item    cost        OrdeID

1   01/01/2021   milk        2              47
2   01/01/2021   apple      3              47
3   01/01/2021   cheese    7              47
4   05/01/2021    milk       4              51
5   05/01/2021    bread     2              51
6   05/01/2021    butter    12             51
.
.
.

And the resultant query will be
orderID dailypaid    total

  47            12             12

  51            18              30
.
.
.
I mean can we group it by orderid? How will the dsum be?
thank you
 
Last edited:

arnelgp

error reading drive A:
Local time
Today, 11:52
Joined
May 7, 2009
Messages
12,825
add the ID to your DSum()
Code:
SELECT yourTable.Date,
    Sum(yourTable.Cost) AS Total,
    DSum("Cost","yourTable","[Date] <= #" & Format([yourTable].[Date],"m/d/yyyy") & "# And [ID] <= [yourTable].[ID]) AS RunningTotal
FROM yourTable
GROUP BY yourTable.Date;
 
Last edited:

ariansman

Registered User.
Local time
Yesterday, 20:52
Joined
Apr 3, 2012
Messages
143
add the ID to your DSum()
Code:
SELECT yourTable.Date,
    Sum(yourTable.Cost) AS Total,
    DSum("Cost","yourTable","[Date] <= #" & Format([yourTable].[Date],"m/d/yyyy") & "# And [ID] <= " & [ID]) AS RunningTotal
FROM yourTable
GROUP BY yourTable.Date;
Thank you very much :)
 

Users who are viewing this thread

Top Bottom