Running sum in Crosstab Query?

[kolsby]

I would like to have a running sum by month (perhaps in a crosstab)

Jan Feb Mar Apr...
5 8 15 21

If Febs result of hours spent is 3 it would add the 5 hours from Jan and the 3 hours in feb fo a result of 8 in Feb.

Is a crosstab the best solution or is there a better way? The results will go through multiple years if that matters.

thanks,
Kolsby

[Phil]

Create a module

Option Compare Database
Option Explicit

Global GBLSum As Double

Public Function InitGlobals()

GBLSum = 0

End Function

Public Function GlobalAdd(ivalue) As Double

GBLSum = GBLSum + ivalue
GlobalAdd = GBLSum

End Function

You can call the GlobalAdd Function from a Query. It will work the first time
you use it. However until you use the InitGlobals function, GBLSum will
continue to increase, so you must use a form or report and run the
InitGlobals on the open of the form or when you change the date

Phil

[PW]

>Create a module
>
>Option Compare Database
>Option Explicit
>
>Global GBLSum As Double
>
>Public Function InitGlobals()
>
> GBLSum = 0
>
>End Function
>
>Public Function GlobalAdd(ivalue) As Double
>
> GBLSum = GBLSum + ivalue
> GlobalAdd = GBLSum
>
>End Function
>
>You can call the GlobalAdd Function from a Query.

I didn't know that was possible! Nice! How do you do dat?

-pw

[Phil]

I have a table Payments, the relevent fields are PaymentDate & PaymentAmount

SQL for Query8 is

SELECT Year([PaymentDate]) AS Yr, Month([PaymentDate]) AS Mnth,
Sum(Payments.PaymentAmount) AS SumOfPaymentAmount
FROM Payments
GROUP BY Year([PaymentDate]), Month([PaymentDate])
ORDER BY Year([PaymentDate]), Month([PaymentDate]);

SQL for Query9 is

SELECT Query8.Yr, Query8.Mnth, GlobalAdd([SumOfPaymentAmount]) AS RunningSum
FROM Query8;

Probably can be done more easily, byt a single query was giving odd results,
and it's 2:45 AM. Good night

Phil

[kolsby]

Great stuff Phil! thanks
One thing i forgot to mention. I might have several customers that i would like to repeat this data for.

Customer|Jan|feb|Mar|Apr|
Cust 1 | 5 | 8| 15| 21|
Cust 2 | 4 | 7 | 20| 32|
etc...

Is this doable using your technique above?
thanks
Kolsby

[Phil]

>
> Great stuff Phil! thanks
> One thing i forgot to mention. I might have several customers that i would
> like to repeat this data for.
>
> Customer|Jan|feb|Mar|Apr|
> Cust 1 | 5 | 8| 15| 21|
> Cust 2 | 4 | 7 | 20| 32|
> etc...
>
> Is this doable using your technique above?
> thanks
> Kolsby
>

You make things very difficult, but it has the advantage that providing there
is a customerID, you don't need to run InitGlobals

OK, a slighly different function

Option Compare Database
Option Explicit

Global GBLSum As Double

Dim OldID As Long

Public Function InitGlobals()

GBLSum = 0

End Function

Public Function GlobalAddX(ivalue, ID As Long) As Double

If ID <> OldID Then
OldID = ID
InitGlobals
End If

GBLSum = GBLSum + ivalue

GlobalAddX = GBLSum

End Function

Query8 becomes

SELECT Sum(Payments.PaymentAmount) AS SumOfPaymentAmount,
Payments.PaymentMethodID, CStr(Year([PaymentDate])) & " - " & CStr
(Format(Month([PaymentDate]),"00")) AS Dt FROM Payments
GROUP BY Payments.PaymentMethodID, CStr(Year([PaymentDate])) & " - " &
CStr(Format(Month([PaymentDate]),"00")), Year([PaymentDate]),
Month([PaymentDate]) ORDER BY Payments.PaymentMethodID, Year([PaymentDate]),
Month([PaymentDate]);

for PaymentMethodID you will use CustomerID
The bit about months & year formats the date as 2011 - 02 for Feb 2011.
Unless you do that, the date order goes wrong to you get month 1, (Jan),
month 11 (Nov), Month 12 (Dec), Month 2 (Feb) etc.

Query9 becomes

TRANSFORM Min(GlobalAddX([SumOfPaymentAmount],[PaymentMethodID])) AS
RunningSum SELECT Query8.PaymentMethodID
FROM Query8
GROUP BY Query8.PaymentMethodID
PIVOT Query8.Dt;

Phil

[kolsby]

Phil,
Thanks again!
I am pretty lost with VBA but i think this gets me real close.
I am getting a "Data type mismatch in criteria expression". error.
My customer id has numbers and letters in them...would that be an issue?

thanks,
KO

[Phil]

>> You make things very difficult, but it has the advantage that providing there
>>
>> is a customerID, you don't need to run InitGlobals
>>
>>
>>
>> OK, a slighly different function

>> Option Compare Database
>> Option Explicit

>> Global GBLSum As Double
>>
>> Dim OldID As Long
>>
>> Public Function InitGlobals()

>> GBLSum = 0

>> End Function
>>

>> Public Function GlobalAddX(ivalue, ID As Long) As Double
>>
>>
>>
>> If ID <> OldID Then
>> OldID = ID
>> InitGlobals
>> End If
>>

>> Phil

> Thanks again!
> I am pretty lost with VBA but i think this gets me real close.
> I am getting a "Data type mismatch in criteria expression". error.
> My customer id has numbers and letters in them...would that be an issue?
>
> thanks,
> KO
>

Most people use an automumber for an ID. This is a long number and
automatically increase by 1 for each new customer you add (or even start to
add). You can't change it and that is why it is garanteed to be unique I
would prefer to call your CustomerID CustomerRef.

So the type mismatch is because in the code I declared OldID as Long and and
in the GlobalAddX I declared ID As Long, expecting CustomerID to be long as
well.

So try removing the words "As Long" in both instances. It should then be
happy to accept either a numeric or string type CustomerID / Ref

Phil

[kolsby]

Thank you! Happy Happy Happy
Thanks Phil! you da man

[kolsby]

If i were to add another field and want to sum by each group using this code is that possible?

Here is really what i am trying to do.

I want to compare Actuals for a project vs Budget.

I have a field indicating an "A" for actuals and a "B" for budget (call it "type")

I would love to add this field to the query if possible and have each have a running sum by month depending on the type.

is this doable?

thanks,
KO