Running totals in query

[dance]

I have a table with 3 fields: Year, Month, Charge.
I want to accumulate totals of the Charge field until the year changes, and then start over. February would have a total of January and February. March would include Jan, Feb, Mar, and so on until December, which would include the totals for the whole year. This is easy in Excel, but how do I do it in Access?
Thanks.

 

[Norris68]

Not the easiest thing in Access, but maybe you could achieve this with a public function.

Public Function CumulativeCharge(ChargeYear As Integer, Charge As Currency) As Currency
Static LastCharge As Currency 'Will initially be zero
Static LastYear As Integer 'Ditto
If LastYear <> ChargeYear Then 'If year has changed
LastCharge = Charge 'First charge this year
LastYear = ChargeYear 'Record new year
Else
LastCharge = LastCharge + Charge 'Add charge
End If
CumulativeCharge = LastCharge 'Return total
End Function

Then, call this as part of your query. It relies on you sorting the data correctly - please tell me your Month field is numeric! BTW: *Frown* at using reserved words as field names (Year & Month)

SELECT [Year], [Month], Charge, CumulativeCharge([Year],[Charge]) AS CumulativeCharge FROM MyTable ORDER BY [Year], [Month]

It will not reset the cumulative charge if you run it more than once on data from a single year. You could always make the static variables module-level variables and write a sub routine to reset them, that you would run just before/after you use the query. Just in case you need the additional information, paste the function into a code module & save it, then paste the query into the SQL view of the query designer - you will need to fill in your table name.

 

[dance]

You are way above me! I don't know what a Public Function is. Can you point me in the right direction to understand this?

 

[Norris68]

Click on Modules in the database window and click New. Copy and paste in the function I gave you (the bit between 'Public Function' and 'End Function'). Save the module.

Create a new query, don't add any tables, but immediately switch to SQL view (View -> SQL View) and copy and paste the line(s) that start 'SELECT [Year]'. Change 'MyTable' to the name of your table - put [square brackets] round it if it has any spaces in the name or used any reserved words. Switch to Datasheet View to see the results.

 

[dance]

I get &quot;compile error. in query expression 'CumulativeCharge([Year],[charge])'&quot;
???
Thanks.

 

[Norris68]

What data types are your Year and Charge fields?

 

[dance]

All 3 fields are numeric

 

[Norris68]

It works fine on mine. Can you paste your entire SQL query (from the SQL view)?

 

[dance]

SELECT [Year], [Month], Charge, CumulativeCharge([Year],[Charge]) AS CumulativeCharge FROM CHARGE_00_03 ORDER BY [Year], [Month]

I also tried it with [CHARGE_00_03]

 

[Norris68]

Go back into the code module and click Debug -> Compile
If it comes up with an error, paste the VBA code here.
BTW: What version of Access are you running?

 

[dance]

I wasn't able to select the message to copy and paste, but it said, &quot;expected list separator or )&quot; and the word &quot;AS&quot; was highlighted in the code.

The version of Access is 2000 (9.0.3821 Sr-1)

Visual Basic 6.3

 

[Norris68]

Did you compile your module?
VBA Editor, Debug -> Compile {ProjectName}

Make it quick, I'm off home in a couple of minutes.

 

[dance]

Yes I chose Debug
Then from the drop down menu it gave me &quot;compile ar&quot; (no other choices of compile) And Ar is the name of my Access database

 

[Norris68]

Did it compile without errors? The Compile menu option will be disabled if so.

Try taking out the function call from the select list - does the following work?
SELECT [Year], [Month], Charge FROM CHARGE_00_03 ORDER BY [Year], [Month]
If so, then switch to Designer View and paste the following into the first available blank Field: cell and try running it again.
CumulativeCharge: CumulativeCharge([Year],[Charge])

Sorry, I'll have to leave you there - it's 17:30 UK time. Hopefully I'll pick it up again in the morning if you still have a problem.