By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,986 Members | 1,944 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,986 IT Pros & Developers. It's quick & easy.

Running total based on date range

jaccess
P: 26
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.
May 9 '07 #1
Share this Question
Share on Google+
12 Replies


jaccess
P: 26
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.
May 10 '07 #2

JConsulting
Expert 100+
P: 603
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

Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2. tblALLBCPIC.DATE, 
  3. tblALLBCPIC.ORDPCK, 
  4. (SELECT Sum(nz([ORDPCK],0)) FROM [tblALLBCPIC] AS a HAVING a.[DATE] <= [tblALLBCPIC].[DATE]) AS RunningSum
  5. FROM tblALLBCPIC
  6. WHERE 
  7. format(tblALLBCPIC.DATE,"mm/dd/yyyy") 
  8. Between 
  9. format([FORMS]![frmBCPIC]![date1], "mm/dd/yyyy") And 
  10. format([FORMS]![frmBCPIC]![date2], "mm/dd/yyyy");
  11.  
May 10 '07 #3

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

give this a testrun

Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2. tblALLBCPIC.DATE, 
  3. tblALLBCPIC.ORDPCK, 
  4. (SELECT Sum(nz([ORDPCK],0)) FROM [tblALLBCPIC] AS a HAVING a.[DATE] <= [tblALLBCPIC].[DATE]) AS RunningSum
  5. FROM tblALLBCPIC
  6. WHERE 
  7. format(tblALLBCPIC.DATE,"mm/dd/yyyy") 
  8. Between 
  9. format([FORMS]![frmBCPIC]![date1], "mm/dd/yyyy") And 
  10. format([FORMS]![frmBCPIC]![date2], "mm/dd/yyyy");
  11.  
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
May 10 '07 #4

JConsulting
Expert 100+
P: 603
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?

Expand|Select|Wrap|Line Numbers
  1. SELECT Sum(nz(a.[ORDPCK],0)) FROM [tblALLBCPIC] AS a HAVING a.[DATE] < Date();
  2.  
May 10 '07 #5

jaccess
P: 26
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.
May 10 '07 #6

JConsulting
Expert 100+
P: 603
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)
May 10 '07 #7

jaccess
P: 26
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.
May 10 '07 #8

JConsulting
Expert 100+
P: 603
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

Expand|Select|Wrap|Line Numbers
  1. SELECT Sum(nz(a.[ORDPCK],0)) FROM [tblALLBCPIC] AS a HAVING a.[DATE] < Date(); 
  2.  
replace the a.[DATE] < Date() with the criteria below.

Expand|Select|Wrap|Line Numbers
  1. format(a.DATE,"mm/dd/yyyy") 
  2. Between 
  3. format([FORMS]![frmBCPIC]![date1], "mm/dd/yyyy") And 
  4. format([FORMS]![frmBCPIC]![date2], "mm/dd/yyyy");
  5.  
May 10 '07 #9

jaccess
P: 26
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
Expand|Select|Wrap|Line Numbers
  1. format(a.DATE,"mm/dd/yyyy") 
line of code?
May 10 '07 #10

JConsulting
Expert 100+
P: 603
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
Expand|Select|Wrap|Line Numbers
  1. format(a.DATE,"mm/dd/yyyy") 
line of code?
Here is a working model for a running sum.

Expand|Select|Wrap|Line Numbers
  1. 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
  2.  
let me know if you can look at yours side by side with this one...and figure out what's up?
J
May 10 '07 #11

jaccess
P: 26
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.

Expand|Select|Wrap|Line Numbers
  1. 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
  2.  
let me know if you can look at yours side by side with this one...and figure out what's up?
J
May 10 '07 #12

jaccess
P: 26
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.

Expand|Select|Wrap|Line Numbers
  1. 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.
May 14 '07 #13

Post your reply

Sign in to post your reply or Sign up for a free account.