Running Total (1 Viewer)

itownson1 · Nov 4, 2019

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 · Nov 4, 2019

Timestamp will probably require # to surround it. ?

itownson1 · Nov 4, 2019

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 · Nov 4, 2019

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

itownson1 · Nov 4, 2019

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 · Nov 4, 2019

You need to order by Timestamp?

itownson1 · Nov 4, 2019

Hi Gasman

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

Gasman · Nov 4, 2019

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

Here is one of mine that works correctly.

Code:

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

Pat Hartman · Nov 4, 2019

The ONLY time you would ever have to enclose a date in # is if you are using a literal.

When you build SQL Strings in code and reference variables or form fields, you are dealing with literals. When you reference table fields, you are not using literals.

In this example:
Runtot: (Select Sum([Qty]) from [QCombinedInventory] as a where a.[Timestamp]<=[QCombinedInventory].[Timestamp])

You are not referencing a literal. You are referencing a field in the query so it never gets converted to a string.

@itownson1,
If you would post a spreadsheet with the raw data, we can give it a shot.

itownson1 · Nov 5, 2019

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 · Nov 5, 2019

You are not taking into account the different sectors.?

You have a running sum, that includes everything?

itownson1 · Nov 5, 2019

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 · Nov 5, 2019

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 · Nov 5, 2019

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 · Nov 5, 2019

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 · Nov 5, 2019

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 · Nov 5, 2019

Creative

, ut as long as it works. Well done.

itownson1 · Nov 5, 2019

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

itownson1 · Nov 5, 2019

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 · Nov 5, 2019

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.?

Running Total (1 Viewer)

Registered User.

Enthusiastic Amateur

Registered User.

Enthusiastic Amateur

Registered User.

Enthusiastic Amateur

Registered User.

Enthusiastic Amateur

Super Moderator

Registered User.

Attachments

Enthusiastic Amateur

Registered User.

Enthusiastic Amateur

Registered User.

Forum Troll

Attachments

Registered User.

Enthusiastic Amateur

Registered User.

Registered User.

Enthusiastic Amateur

Similar threads

Users who are viewing this thread