Running Total with Text and Date field (1 Viewer)

[Nevsky78]

Dear all,

I have searched in depth through the forums for an answer to this but all running sum posts do not quite answer my problem.

I have a table ("tbl_idq_all") with a text field for product codes ("scode"), a date field (dd/mm/yyyy) and a quantity field ("po_qty"). This table therefore holds future receipts of stock for products.

What I am having trouble doing is create a running total of [po_qty] based on [scode] and [Date].

A good example is stock code 10254. This has a quantity of 40,032 arriving 01/06/2013 and a quantity of 30,096 arriving 01/09/2013.

Therefore the running totals should read:

scode | Date | po_qty | RunningTotal
10254 | 01/06/2013 | 40032 | 40032
10254 | 01/09/2013 | 30096 | 70128

As you can see from the attached DB I have 70128 repeated twice in the RunningTotal column.

And help would be very gratefully received!

Nick

 

[SOS]

Change your query's SQL to this:

SELECT tbl_idq_all.scode, tbl_idq_all.Date, tbl_idq_all.po_qty, DSum("po_qty","tbl_idq_all","[Date] > #" & [Date] & "# And scode='" & [scode] & "'")+Nz([po_qty],0) AS RunningTotal
FROM tbl_idq_all
ORDER BY tbl_idq_all.scode, tbl_idq_all.Date DESC;

You didn't include sorts which are necessary for it to work properly and you didn't add the current po_qty. I did that for you and it appears to be working fine now.

 

[Nevsky78]

Hi SOS,

That's certainly an improvement, thank you! I did have the order in there previously but stupidly left it out on the uploaded version (I've also changed it to ascending date order)

However, for the scode 10254 example the running totals look a little strange.

scode |Date | po_qty | RunningTotal
10254 | 01/06/2013 | 40032 | 110160
10254 | 01/09/2013 | 30096 | 100224

Is that to do with the NZ current quantity?

The running totals should be 40032 and 70128 respectively.

Regards,
Nick

 

[SOS]

Hi SOS,

That's certainly an improvement, thank you! I did have the order in there previously but stupidly left it out on the uploaded version (I've also changed it to ascending date order)

However, for the scode 10254 example the running totals look a little strange.

scode |Date | po_qty | RunningTotal
10254 | 01/06/2013 | 40032 | 110160
10254 | 01/09/2013 | 30096 | 100224

Is that to do with the NZ current quantity?

The running totals should be 40032 and 70128 respectively.

Regards,
Nick

Change the query SQL to this:

SELECT tbl_idq_all.scode, tbl_idq_all.Date, tbl_idq_all.po_qty, DSum("po_qty","tbl_idq_all","[Date] < #" & [Date] & "# And scode='" & [scode] & "'")+Nz([po_qty],0) AS RunningTotal
FROM tbl_idq_all
ORDER BY tbl_idq_all.scode, tbl_idq_all.Date;

And when I do that I get:

qry_running_total
scode  Date        po_qty  RunningTotal
10254  6/1/2013  40032
10254  9/1/2013  30096   70128

 

[Nevsky78]

Hi SOS,

Apologies for the delay in getting back to you.

I pasted that SQL in there but the totals are now blank. I think this is to do with your date format being US and my date format being UK.

I guess I need to convert my UK date to US for this to work?

Nick

 

[Nevsky78]

Actually, even when I do that I still get an odd result!

      qry_running_total    scode Date po_qty RunningTotal      10254 06/01/2013 40032 110160     10254 09/01/2013 30096 100224

I've attached the current database. Could this be a Database Sort Order issue? Mine is General Legacy.

Nick

 

[SOS]

Actually, even when I do that I still get an odd result!

      qry_running_total    scode Date po_qty RunningTotal      10254 06/01/2013 40032 110160     10254 09/01/2013 30096 100224

I've attached the current database. Could this be a Database Sort Order issue? Mine is General Legacy.

Nick

I don't think it is that issue. Are you somewhere where the dates use the dd/mm/yyyy format instead of the US format? Here's what I see when I run your query.

 

[Nevsky78]

Hi SOS,

I was using the UK format for dates i.e. dd/mm/yyyy but I did convert them all to the US date format of mm/dd/yyyy to see if that helped.

I think it must be some regional setting somewhere in that case...

 

[SOS]

Change your SQL to this and try:

SELECT tbl_idq_all.scode, Format([Date],"mm\/dd\/yyyy") AS poDate, tbl_idq_all.po_qty, DSum("po_qty","tbl_idq_all","[Date] < #" & [Date] & "# And scode='" & [scode] & "'")+Nz([po_qty],0) AS RunningTotal
FROM tbl_idq_all
ORDER BY tbl_idq_all.scode, Format([Date],"mm\/dd\/yyyy");