Create Running Totals

[dascooper]

I am trying to create a query that provides a running total for the number of planned events. I have the following code that, when run, gives me a data type missmatch. Below is the subselect from the sql code. The problem lies in the use of dates as a comparison. I want to include all the previous events to have a current running sum. The problematic code is below:



Plan: (select sum( [number planned]) from [q counts] nn where dateserial([nn].[year],[nn].[month],1) <= dateserial([q counts].[year],[q counts].[Month],1) and [nn].[Function_Names] = [q counts].[Function_Names])

I have tried comparing year to year and month to month (which works) but doesn't give me the correct amounts (the data goes over several years).

Any help/suggestions. The table q counts contains just four fields:

Function_Names
number planned
Year
Month

Thanks!

[ajetrumpet]

does the solution here help? https://www.accessforums.net/forms/h...orm-12533.html

[dascooper]

I get the concept and have tried to build it into my query. It seems to work where the year is the same. However, I need to be able to have a running total across years. I can't quite get it to work right.

[ajetrumpet]

I get the concept and have tried to build it into my query. It seems to work where the year is the same. However, I need to be able to have a running total across years. I can't quite get it to work right.

For some reason, a lot of people don't understand how to make running totals work. How about posting some data you've now and then another sample of how it should look when it's all over??

be sure to cover all the possible pitfalls/data inconsistencies that one might run into.

[dascooper]

Here is before and after shot of what I am trying to do:

Rev Due Date Function_Names MCount
Customer Service 0
Power Production 0
Transmission & Distribution 0
6/1/2011 Power Production 1
7/1/2011 Power Production 1
8/1/2011 Power Production 1
9/1/2011 Power Production 9
10/1/2011 Power Production 2
10/2/2011 Power Production 1
6/1/2012 Power Production 1
6/1/2013 Power Production 1
6/1/2014 Power Production 1


Rev Due Date Function_Names Running Total
2011 06 Power Production 1
2011 07 Power Production 2
2011 08 Power Production 3
2011 09 Power Production 12
2011 10 Power Production 14
2011 10 Power Production 15
2012 06 Power Production 16
2013 06 Power Production 17
2014 06 Power Production 18


The only change is that the months should be summed (note two records for 2011/10). I'm not so worried about getting it by each function name (I can deal with that later). The ultimate goal is to create a set of charts to show progress of planned vs actual number of closed projects.

[ajetrumpet]

cooper,

this can be solved exactly the same way this one was: https://www.accessforums.net/forms/h...orm-12533.html

the sql I gave there was:

SELECT tblMoney.fldcode, tblMoney.flddate, 
 
[fldvalue] + 
 
iif(DSum("fldValue","tblMoney","[fldDate] < #" & [flddate] & "#") is null, 0, 
DSum("fldValue","tblMoney","[fldDate] < #" & [flddate] & "#")) 
 
+ 
 
IIf((DSum("fldValue","tblMoney","[fldDate] = #" & [flddate] & "# AND [fldCode] < " & [fldcode])) Is Null, 
0, DSum("fldValue","tblMoney","[fldDate] = #" & [flddate] & "# AND [fldCode] < " & [fldcode])) AS Expr1
 
FROM tblMoney
 
ORDER BY tblMoney.fldDate;

this portion:

[fldvalue] +

ensures that the evaluated record's total field is added to the mix. The next section:

iif(DSum("fldValue","tblMoney","[fldDate] < #" & [flddate] & "#") is null, 0, 
DSum("fldValue","tblMoney","[fldDate] < #" & [flddate] & "#")) 
 
+

sums all of the previous records where the date is LESS than the date of the record being evaluated.

then you have the last portion:

IIf((DSum("fldValue","tblMoney","[fldDate] = #" & [flddate] & "# AND [fldCode] < " & [fldcode])) Is Null, 
0, DSum("fldValue","tblMoney","[fldDate] = #" & [flddate] & "# AND [fldCode] < " & [fldcode])) AS Expr1

which is adding to the mix (if applicable), all the totals that have the SAME date as the evaluated record, but a lower ID number (e.g. - the lower ID numbers were obviously entered before higher ones)

and then of course you have to use an ORDER BY clause, which has to be executed first otherwise the totaling technique would not work:

ORDER BY tblMoney.fldDate, tblMoney.fldcode;

that's a different clause than originally posted, only because the post is a guess without actually working it.

So in your case, you'll use "Rev Due Date" as the date field and "MCount" as the field value from the above example. I will assume that also have an ID in an autonumber field somewhere?? If you do, you can copy that sql verbatim with name substitutes only. If you have no ID field, you'll have to make one, or another unique identifier. These kinds of things really can't be done without uniqueness, because the complexing would probably start overwhelming the sql engine.

[dascooper]

I guess the question is how to group the data by month?

[ajetrumpet]

I guess the question is how to group the data by month?

oh...sorry about that. In that example, I stacked queries. the first one selected DISTINCT records. You should do that too...select distinct records based on the year and month. e.g. - one way to do that would be:

select format([datefield], "mm/yyyy")

or something of that nature. producing a two-tier stack like that will make it easier on the db engine, and will also eliminate the hassle of reading incredibly long and confusing sql statements, not to mention the fact that it eliminates the need for a GROUP BY clause which is always a headache anyway.

stacking is very much underrated! I'd use it...

give it a go and if you run into a snag, post back we'll help you out.

[dascooper]

I'm still not quite getting the results I'm looking for. I haven't used a crosstab query before, but the layout gets me very near what I'm trying to do. The problem is then applying the dcount function in a crosstab sql. I think I have it close, but am still getting syntax errors. The starting point (before applying the crosstab looks like this:

q Initiatives_CrosstabExpr1Total Of NumberCustomer ServicePower ProductionTransmission & Distribution
753223202011 061
1
2011 071
1
2011 081
1
2011 099
9
2011 103
3
2012 061
1
2013 061
1
2014 061
1



This is the code to obtain the above data.

TRANSFORM Count([q Initiatives].[Number]) AS CountOfNumber
SELECT Format([Rev Due Date],"yyyy mm") AS Expr1, Count([q Initiatives].[Number]) AS [Total Of Number]
FROM [q Initiatives]
GROUP BY Format([Rev Due Date],"yyyy mm")
PIVOT [q Initiatives].[Function_Names];


This is the code with the dcount function Applied (again, not quite working. The bold text shows where Access seems to be having trouble with.

TRANSFORM Count([q Initiatives].[Number]) AS CountOfNumber
SELECT Format([Rev Due Date],"yyyy mm") AS Period, dCount("*","q Initiatives","[Period] <=format([Rev Due Date],"yyyy mm") AS [Total Of Number]")
FROM [q Initiatives]
GROUP BY Format([Rev Due Date],"yyyy mm")
PIVOT [q Initiatives].[Function_Names];


Thanks for your help. I've been struggling with this for some time (I had a similar issue a few years ago, I haven't been able to apply the same logic to solving this problem).

David

[ajetrumpet]

I didn't give advice on a crosstab query. I'm going to bow out of this now, because everything you need in order to do this I already provided. You're more than welcome to use it to your advantage.

good luck to ya.