Running Total - Running Sum

[Jacques]

I'm trying to get a query to do a running sum. What's happening is, I have
two queries:
Query 1 = Select Query that totals most of the fields and sums the Hours
field
Query 2 = pulls from Query 1 and puts the data in crosstab with the
MonthYear as the column headers
The value is the SumOfHours pulled from Query 1

What I need is for Query 1 to give me a running total/running sum per month,
so that the end report can be a spreadsheet with the MonthYear as column
headers in the report.

I've seen where the DSum function on Microsoft's site will supposedly do a
running sum in a query, but the explanation they give for using the DSum
function never works for me.

Can anyone show me how to write it correctly, or better yet, show me another
way to make a query do a running sum.

Thanks beforehand,

Jacques

 

[[MVP] S.Clark]

Assuming that Month and Year are numeric values

Dsum([fieldvalue], [datasource], "[MyMonth] < " & [MyMonth] & "AND [MyYear]
= " & [MyYear])

This won't handle wrapping around the corner of a year end, but maybe will
get you started.

--
Steve Clark, Access MVP
FMS, Inc
http://www.fmsinc.com/consulting
Professional Access Database Repair
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

 

[Jacques]

Fellas,
My apologies for not getting back to you sooner. Let me be more
specific about what I'm doing.

I'm making a report that has a list of branch offices on the left side of
the page. Each branch office has its own row.
The second column shows the number of employees that have worked at that
office.
The next twelve columns are the month/year of the last 12 months, and the
below info is the total hours worked of the combined employee count.

The problem is:
1) Every column is a running total of the previous column.
2) The report should be able to be generated monthly, meaning the twelve
months being shown as column headers would change every month.
3) Its possible for branch offices to be added.

Any help (and I mean any) would be greatly appreciated.

 

[Jacques]

Fellas,
My apologies for not getting back to you sooner. Let me be more specfic
about what I'm doing.

I'm making a report that has a list of branch offices on the left side of
the page. Each branch office has its own row.
The second column shows the number of employees that have worked at that
office.
The next twelve columns are the month/year of the last 12 months, and the
below info is the total hours worked of the combined employee count.

The problem is:
1) Every column is a running total of the previous column.
2) The report should be able to be generated monthly, meaning the twelve
months being shown as column headers would change every month.
3) Its possible for branch offices to be added.

Any help (and I mean any) would be greatly appreciated.