Take away quantity of previous day from running sum

[Podder]

Hi,
I am trying to create a query from one table where quantities are added to a basket each day, these quantities are running sums of all other previous quantities in the same basket.
What I am trying to achieve is, when I get the new daily quantities, I want to remove the previous days quantities in a new column using a query which will then show the days quantity for each basket.
However, there are often new baskets added each day, so these need to be included into the daily quantities.
E.g
BasketTBL
BasketNo Colour DateSent Quantity
1 Red 23/10/21 200
2 Blue 23/10/21 100
1 Red 24/10/21 250
2 Blue 24/10/21 150
3 Yellow 24/10/21 130

So when I get the quantities on the 24/10/21, I want the following result from the query
BasketNo Colour DateSent Quantity
1 Red 24/10/21 50
2 Blue 24/10/21 50
3 Yellow 24/10/21 130



Is this possible?

[Bob Fitz]

Perhaps:

SELECT [BasketsTBL].BasketNo, [BasketsTBL].Colour, [BasketsTBL].DateSent, Nz(DSum("qty","Baskets","Colour = '" & [Colour] & "' AND DateSent<#" & [DateSent] & "#"),0) AS Prev, [qty]-[Prev] AS BalFROM BasketsTBL
WHERE ((([BasketsTBL].DateSent)=#10/24/2021#));

[Bob Fitz]

Sorry. That should have been:

SELECT [BasketsTBL].BasketNo, [BasketsTBL].Colour, [BasketsTBL].DateSent, Nz(DSum("qty","BasketsTBL","Colour = '" & [Colour] & "' AND DateSent<#" & [DateSent] & "#"),0) AS Prev, [qty]-[Prev] AS BalFROM BasketsTBL
WHERE ((([BasketsTBL].DateSent)=#10/24/2021#));

[Micron]

Maybe your quantity added/removed should be in its own field so that if you begin with a starting number (e.g.200) the balance is the sum of the ins and outs:

Basket	Color	In_Out
1	Red	25
2	blue	-20
3	green	40
1	Red	-10
3	green	10
2	blue	30

The balances are red 15, blue 10, green 50. Also, if this were a table of transactions you would not have a field for the color as this is an attribute of the basket (where the beginning and adjusted balances would also be kept). Balances may not be exactly what you're after but the principle should be the same.

[Podder]

Sorry. That should have been:

SELECT [BasketsTBL].BasketNo, [BasketsTBL].Colour, [BasketsTBL].DateSent, Nz(DSum("qty","BasketsTBL","Colour = '" & [Colour] & "' AND DateSent<#" & [DateSent] & "#"),0) AS Prev, [qty]-[Prev] AS BalFROM BasketsTBL
WHERE ((([BasketsTBL].DateSent)=#10/24/2021#));

Bob,
Doesnt seem to work, keeps coming up with "Select statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect"
Been through the statement and there are no reserved words or misspelled names so not sure what issue is.
If the above select statement is correct then ill just have to keep working with it.

[CJ_London]

looks like you need a space before FROM

AS BalFROM BasketsTBL

[Bob Fitz]

I believe that Ajax has spotted the problem.
For the sake of completeness, I have attached a working copy.

[Podder]

Bob,
I looked at your attached copy, the issue I have is, if I now add another 3 entries for the next day we have the following:

BasketNo	Colour	DateSent	Qty
1	Red	23/10/2021	200
2	Blue	23/10/2021	100
1	Red	24/10/2021	250
2	Blue	24/10/2021	150
3	Yellow	24/10/2021	130
1	Red	25/10/2021	270
2	Blue	25/10/2021	180
3	Yellow	25/10/2021	160

So from the query we should have in the Bal Red = 20, Blue = 30 & Yellow = 30, however I am getting Red = -180, Blue = -70 & Yellow = 30
I believe the problem is that your solution sums the previous entries of each basket but the last entry is the new total,
hence from above BASKET RED 24/10/21 running total = 250 and this should be the value in which the Bal should be taken, currently the solution sums both previous entries.
Is there a way to make Prev the last entry only??

Hope this makes sense.

[Bob Fitz]

See Query1 in attached db.

[Podder]

Bob,
Excellent, exactly what I was looking for.
Thank you very much

[Bob Fitz]

Bob,
Excellent, exactly what I was looking for.
Thank you very much

You're welcome. Always pleased to help if I can ��

[Podder]

Bob,
From previous post, your solution worked excellently, however when I go from 30/10/21 to 1/11/21 your solution doesnt work.
Im sure theres a simple work a round.
Any advice would be appreciated

[Bob Fitz]

Podder

Bob,
Im sure theres a simple work a round.

Unfortunately, I haven't found a "simple" work around but I do have some sort of solution to offer you.
It involves the use of forms. IMHO users should not be given access to tables and queries, so the use of forms might actually be better.
Let me know if it suits your needs