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

 

[Jamie Collins]

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.

By 'correctly', I assume you mean avoiding proprietary syntax <g>.

Consider this simple example:

CREATE TABLE Test9 (
effective_date DATETIME NOT NULL UNIQUE,
amount CURRENCY NOT NULL, CHECK(amount >= 0.00)
)
;
INSERT INTO Test9 (effective_date, amount)
VALUES (#2006-01-01 00:00:00#, 5)
;
INSERT INTO Test9 (effective_date, amount)
VALUES (#2006-01-02 00:00:00#, 11)
;
INSERT INTO Test9 (effective_date, amount)
VALUES (#2006-01-03 00:00:00#, 14)
;
INSERT INTO Test9 (effective_date, amount)
VALUES (#2006-01-04 00:00:00#, 2)
;

Here are a couple of standard SQL constructs which will give a running
total:

SELECT T2.effective_date,
SUM(T1.Amount) AS amount_to_date
FROM Test9 AS T1, Test9 AS T2
WHERE T1.effective_date <= T2.effective_date
GROUP BY T2.effective_date;

SELECT T2.effective_date, (
SELECT SUM(T1.Amount)
FROM Test9 AS T1
WHERE T1.effective_date <= T2.effective_date
) AS amount_to_date
FROM Test9 AS T2;

As a courtesy, here's some VBA to recreate the above example:

Sub runningt()
Dim cat
Set cat = CreateObject("ADOX.Catalog")
With cat
.Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\DropMe.mdb"
With .ActiveConnection
' Note: this table is missing a key!
.Execute _
"CREATE TABLE Test9 (effective_date DATETIME" & _
" NOT NULL UNIQUE, amount CURRENCY NOT NULL," & _
" CHECK(amount >= 0.00));"
.Execute _
"INSERT INTO Test9 (effective_date, amount)" & _
" VALUES (#2006-01-01 00:00:00#, 5);"
.Execute _
"INSERT INTO Test9 (effective_date, amount)" & _
" VALUES (#2006-01-02 00:00:00#, 11);"
.Execute _
"INSERT INTO Test9 (effective_date, amount)" & _
" VALUES (#2006-01-03 00:00:00#, 14);"
.Execute _
"INSERT INTO Test9 (effective_date, amount)" & _
" VALUES (#2006-01-04 00:00:00#, 2);"

Dim rs
Set rs = .Execute( _
"SELECT T2.effective_date, SUM(T1.Amount)" & _
" AS amount_to_date FROM Test9 AS T1, Test9" & _
" AS T2 WHERE T1.effective_date <= T2.effective_date" & _
" GROUP BY T2.effective_date")
MsgBox rs.GetString
rs.Close

Set rs = .Execute( _
"SELECT T2.effective_date, (SELECT SUM(T1.Amount)" & _
" FROM Test9 AS T1 WHERE T1.effective_date" & _
" <= T2.effective_date ) AS amount_to_date" & _
" FROM Test9 AS T2;")
MsgBox rs.GetString
rs.Close

End With
Set .ActiveConnection = Nothing
End With
End Sub

Jamie.

--

 

[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.