Running Total

[itownson1]

I am trying to get a query to give me a running total....and failing miserably.

My select query is getting it's data from a union query (QCombinedInventory) and a table. (Goods)

I want a running total of Qty.
I have scoured the internet and have now lost the will.
This is what I have at the moment and it gives me "#Error"

RunTot: DSum("Qty","QCombinedInventory","[Timestamp]<=" & [Timestamp] & "")

Both Timestamp and Qty are in the Combined Query.

Ian

 

[Gasman]

Timestamp will probably require # to surround it. ?

 

[itownson1]

I cant get it to work in the report running sum either.

Control Source = Qty
Text Format = Plain Text
Running Sum = Over all
Enabled = Yes

 

[Gasman]

Upload your DB. I might not be able to open it as I only have 2007, but others could likely help.

 

[itownson1]

Struggling to upload, but I will try again when I get home from work.

I have tried this statement.
Runtot: (Select Sum([Qty]) from [QCombinedInventory] as a where a.[Timestamp]<=[QCombinedInventory].[Timestamp])

I get numbers, so a sense of achievement has been reached, it is just they are all wrong.

GoodsID Timestamp Qty Sector RunningSum
2 04/11/2019 14:54:43 50 Adjust 402
2 04/10/2019 21:02:00 10 Production 346
2 03/10/2019 10:41:00 12 Goods In 318
2 02/10/2019 21:00:00 3 Production 256
2 01/10/2019 01:00:00 11 Goods In 11

 

[Gasman]

You need to order by Timestamp?

 

[itownson1]

Hi Gasman

Thank you for your patience with me on this.
The end of my SQL does say
ORDER BY QCombinedInventory.Timestamp;

 

[Gasman]

I did say that you would probably need to surround timestamp with # ?

Here is one of mine that works correctly.

SELECT Emails.ID, Emails.TransactionDate, Emails.CMS, Emails.Client, Emails.Amount, DSum("[Amount]","Emails","[CMS]=" & [CMS] & " AND ID < " & [ID]) AS PrevBal, [Amount]+nz([PrevBal],0) AS NewBal, DSum("[Amount]","Emails","[CMS]=" & [CMS] & " AND TransactionDate < #" & [Transactiondate] & "#") AS PrevBalDate
FROM Emails
ORDER BY Emails.ID, Emails.TransactionDate, Emails.CMS;

HTH

 

[itownson1]

Hopefully I have attached the data I am getting.
It actually looks like it is adding up the "Goods In" data ok, but when the "production" data is added it throws it out. Maybe formatting on my production table is incorrect.

Lets investigate!!
(Sorry, I really need to stop watching Noddy the Toyland Detective.)

 

[Gasman]

You are not taking into account the different sectors.?

You have a running sum, that includes everything?

 

[itownson1]

Yes, I am trying (and failing to crawl before I can run.)
In an ideal world, I want it to add the "Goods In" and then subtract the "Production". I also want it to reset when a stock adjustment has been made.
I just thought it would be easier to figure out how I could get a running sum to work first and then move on from that.
I think it is because I have the production side running through a Union query that may be the issue with the data on production. I am unsure and my hair is turning grey by the minute.

 

[Gasman]

Well you have a running total, but when the timestamp is exactly the same (rows 10 - 12) the value goes awry?

You need to take into account the order of the data as well (perhaps the AutoNumber ID if you have one?)

You will see from my code I used ID as that table just has short dates, no need for time, so that takes into account the order of teh data presented.

 

[itownson1]

I tried looking at putting an autonumber ID on, but strugglued because the query is getting its data from the "Goods In" table and a union query containing the production data.

 

[vba_php]

itownson,

why did you upload an excel file if u r trying to do this in access? see this database. does that help? you might have to call a VBA function to do what you want, although i'm not sure about that.

 

[itownson1]

Stage 1 of the mission complete.
Because a few products can be used in the same production batch it obviously gives them the same Timestamp.
My REALLY UGLY work around was to use the GoodsID number and turn that into seconds. Then add that to the Timestamp. This meant every Timestamp in production a different Time by a second or two.
It then worked perfectly.
I know you all have your head in your hands cursing me, but... It worked.

 

[Gasman]

Creative , ut as long as it works. Well done.

 

[itownson1]

Don't go away Gasman, we have stage 2 - stage 20,000 to complete yet!!

 

[itownson1]

Stage 2!!! Do I put this under a new topic or is it ok here?

How do I group the query with a running sum. When I filter the data by product, it still running sums the entire datasheet rather than what I am filtering.

 

[Gasman]

Include the product in the summing criteria?, however I'm not sure you will get a running sum now, as you will only get one entry per product, so a simple sum will do that.?

 

[itownson1]

It is only sample data I have put in, so can add more data to get the running total.

Where would I add the "product" to the summing criteria for it to group by "product"?

RunningSum: Sum((Select Sum([QtyAdjusted]) from [QCombinedInventory] as a where a.[Timeadjust]<=[QCombinedInventory].[Timeadjust]))