Running total based on date range

[jaccess]

Hello all,

I am trying to create a running total based on a specific date range that is to be entered into a form.

I currently have the form set up with 2 text boxes (date1 and date2) which are the input for my start and end date, a table (tblALLBCPIC) with a DATE field and ORDPCK field (what I need a running sum of). There are other fields in the table but they shouldn’t matter.

Here is the SQL for my query so far:

SELECT tblALLBCPIC.DATE, tblALLBCPIC.ORDPCK, (SELECT Sum([ORDPCK]) FROM [tblALLBCPIC] AS [tblALLBCPIC_1] WHERE [tblALLBCPIC_1].[DATE] <= [tblALLBCPIC].[DATE]) AS RunningSum
FROM tblALLBCPIC;

I have added and played around with various configurations of this including adding

Between [FORMS]![frmBCPIC]![date1] And [FORMS]![frmBCPIC]![date2] into the criteria for the date field .

My results will usually give me a running total of the entire table (2005-2007) instead of for just the date range that was specified.

It is my understanding that the Dsum function will do pretty much the same thing but take much longer to calculate. ?

Another possible factor for failure so far is that the table with the data does have sum NULL fields/entries. I do know about the Nz function and am pretty sure something like this would work (Nz([ORDPCK],'0')) maybe somewhere.

Any help would be greatly appreciated. If you require any more info please let me know, thanks in advance.

[jaccess]

Something I have noticed in trying to figure out how to make this work is, that the function I am using is going through every record in my table to make the running sum/total.

Perhaps I need to use the Between date range criteria -
Between [FORMS]![frmBCPIC]![date1] And [FORMS]![frmBCPIC]![date2] in the running total function instead of in the criteria.

I will give that a try and see what happens.

[JConsulting]

Something I have noticed in trying to figure out how to make this work is, that the function I am using is going through every record in my table to make the running sum/total.

Perhaps I need to use the Between date range criteria -
Between [FORMS]![frmBCPIC]![date1] And [FORMS]![frmBCPIC]![date2] in the running total function instead of in the criteria.

I will give that a try and see what happens.

give this a testrun

SELECT 
tblALLBCPIC.DATE, 
tblALLBCPIC.ORDPCK, 
(SELECT Sum(nz([ORDPCK],0)) FROM [tblALLBCPIC] AS a HAVING a.[DATE] <= [tblALLBCPIC].[DATE]) AS RunningSum
FROM tblALLBCPIC
WHERE 
format(tblALLBCPIC.DATE,"mm/dd/yyyy") 
Between 
format([FORMS]![frmBCPIC]![date1], "mm/dd/yyyy") And 
format([FORMS]![frmBCPIC]![date2], "mm/dd/yyyy");
 

[jaccess]

Thank you for taking the time to post a response, your assistance is very much appreciated.

give this a testrun

SELECT 
tblALLBCPIC.DATE, 
tblALLBCPIC.ORDPCK, 
(SELECT Sum(nz([ORDPCK],0)) FROM [tblALLBCPIC] AS a HAVING a.[DATE] <= [tblALLBCPIC].[DATE]) AS RunningSum
FROM tblALLBCPIC
WHERE 
format(tblALLBCPIC.DATE,"mm/dd/yyyy") 
Between 
format([FORMS]![frmBCPIC]![date1], "mm/dd/yyyy") And 
format([FORMS]![frmBCPIC]![date2], "mm/dd/yyyy");
 

I gave it a test run and it did not give me any results for the running total, and it still listed all records in the table instead of what was in the date range on the form. I believe you are on the right track though.

I have not seen "AS a HAVING a" used before could you explain what it does?

I was not able to figure out a workable solution by trying what I had mentioned previously. Logically it seem fairly simple, obviously I am missing some very important code.

Thanks again

[JConsulting]

Thank you for taking the time to post a response, your assistance is very much appreciated.



I gave it a test run and it did not give me any results for the running total, and it still listed all records in the table instead of what was in the date range on the form. I believe you are on the right track though.

I have not seen "AS a HAVING a" used before could you explain what it does?

I was not able to figure out a workable solution by trying what I had mentioned previously. Logically it seem fairly simple, obviously I am missing some very important code.

Thanks again

Right Track? Maybe...we'll see

I'm just aliasing the table name with a to make joins simple. you had something in there before..something like tblALLBCPIC1 or something. I just shortened it to a.

when you put your subquery into a SQL window by itself...will it return anything?

SELECT Sum(nz(a.[ORDPCK],0)) FROM [tblALLBCPIC] AS a HAVING a.[DATE] < Date();
 

[jaccess]

when you put your subquery into a SQL window by itself...will it return anything?

Yes it gives me one record with the total for all the records in the table.

[JConsulting]

Yes it gives me one record with the total for all the records in the table.

ok...so if you now want to only return dates between a certain range, we need to add the criteria for that there...

then once you get those results...We can link them in using the ID fields.

Are you enjoying learning about this stuff and how to "build" it all up? :o)

[jaccess]

Are you enjoying learning about this stuff and how to "build" it all up? :o)

Mostly. My background is in web design, SQL seems a bit more difficult (and powerful) then what I am used to. It has been a steep learning curve for me.

So now I need to figure out how/where to add the date range criteria is what you are telling me? Sorry for not really "getting it", it is all so new and challenging for me.

[JConsulting]

Mostly. My background is in web design, SQL seems a bit more difficult (and powerful) then what I am used to. It has been a steep learning curve for me.

So now I need to figure out how/where to add the date range criteria is what you are telling me? Sorry for not really "getting it", it is all so new and challenging for me.

Around here (my work) it all runs together. Web Design, database, SQL, boredom!

and about the figuring out part....yep

You want to edit this SQL

SELECT Sum(nz(a.[ORDPCK],0)) FROM [tblALLBCPIC] AS a HAVING a.[DATE] < Date(); 
 

replace the a.[DATE] < Date() with the criteria below.

format(a.DATE,"mm/dd/yyyy") 
Between 
format([FORMS]![frmBCPIC]![date1], "mm/dd/yyyy") And 
format([FORMS]![frmBCPIC]![date2], "mm/dd/yyyy");
 

[jaccess]

Ok, it is definnatly giving me a different number now, probably to one I need.

Is there a way to display all of the records though, to create a running total not just a total?

Also in order to show the running total for the month to date (based on the same date range) would I need to modify the

format(a.DATE,"mm/dd/yyyy") 

line of code?

[JConsulting]

Ok, it is definnatly giving me a different number now, probably to one I need.

Is there a way to display all of the records though, to create a running total not just a total?

Also in order to show the running total for the month to date (based on the same date range) would I need to modify the

format(a.DATE,"mm/dd/yyyy") 

line of code?

Here is a working model for a running sum.

SELECT M.ID, M.TDate, M.Feet, DSUM(""1"", ""M"", ""ID <= "" & M.ID & "") as Running_Count FROM M where [tDate] >= #1/1/2007#  GROUP BY M.ID, MAR.TDate, M.Feet ORDER BY M.ID
 

let me know if you can look at yours side by side with this one...and figure out what's up?
J

[jaccess]

Thanks again so much for your help and patience. I will check it out on Monday and post my results.

Have a great weekend! ( I know its only Thursday but no work for me tomorrow)

J

Here is a working model for a running sum.

SELECT M.ID, M.TDate, M.Feet, DSUM(""1"", ""M"", ""ID <= "" & M.ID & "") as Running_Count FROM M where [tDate] >= #1/1/2007#  GROUP BY M.ID, MAR.TDate, M.Feet ORDER BY M.ID
 

let me know if you can look at yours side by side with this one...and figure out what's up?
J

[jaccess]

Ok, I took a look at your example and tried to rework it with my info, with no luck. Here is what I came up with based on your model.

SELECT tblALLBCPIC.ORDPCK, tblALLBCPIC.DATE, DSUM(""1"", ""tblALLBCPIC"", ""ORDPCK <= "" & tblALLBCPIC.ORDPCK & "") as Running_Count FROM tblALLBCPIC where [DATE] >= #1/1/2007#  GROUP BY tblALLBCPIC.ORDPCK, tblALLBCPIC.DATE ORDER BY tblALLBCPIC.ORDPCK

I get a syntax error for the DSUM(""1""...... part, apparently I am missing an operator.

Once again if you have the time could you hold my hand and walk me through this please.