how to get a summary querry (1 Viewer)

ariansman

Registered User.
Local time
Today, 02:38
Joined
Apr 3, 2012
Messages
157
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
Today, 02:38
Joined
Oct 29, 2018
Messages
21,357
Hi. Look up Totals query and Running Sum query.

Sent from phone...
 

Isaac

Lifelong Learner
Local time
Today, 02:38
Joined
Mar 14, 2017
Messages
8,738
For the first item, study on Aggregate queries. For the second, search this site on Running Sum in Query
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:38
Joined
May 7, 2009
Messages
19,169
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
Today, 02:38
Joined
Apr 3, 2012
Messages
157
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

..forever waiting... waiting for jellybean!
Local time
Today, 17:38
Joined
May 7, 2009
Messages
19,169
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
Today, 02:38
Joined
Apr 3, 2012
Messages
157
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