Monthly Totals AND Running Totals in Query

[Guest]

I am trying to represent Monthly and Overall Labor totals on a Chart. My
query calculates the monthly totals just fine. How do I calculate a running
total? Ideally I would like to show a Bar and Line Chart. The Bar
illustrates Monthly Totals, the Line illustrates Running Total.

Fields Used:

TimeSheetDate, ProjectID, Hours

Desired results:

Month MoTot RunTot
Jan 100 100
Feb 200 300
Mar 150 450

Thanks

 

[Al Campagna]

Randy,
Place an text control on the report based (ControlSource) on the MoTot field. Don't
use the MoTot name, use something elsse like RunSumMoTot.
Set that control's Running Sum property to OverGroup or OverAll to suit your needs.
--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

 

[Guest]

Thanks for the reply. My report is calculating all controls properly (MTD
and YTD). My problem is with the CHART placed in the footer of the report.
When I try to populate the CHART utilizing the crosstab query, I can't
include a Running Total (YTD) row heading correctly. Based on answers to
someone elses question in the query forum I have tried implementing a
sub-query to perform the YTD without success. Also, when trying to add a
sub-query, I receive an error stating Multi-Level GroupBy statements exist in
the sub-query.

Another thing...field TimeSheetDate exists in a different table than fields,
ProjectID and Hours. Not sure if this could be causing problems.

 

[John W. Vinson]

On Fri, 20 Apr 2007 12:52:05 -0700, Randy Brown <Randy

I am trying to represent Monthly and Overall Labor totals on a Chart. My
query calculates the monthly totals just fine. How do I calculate a running
total? Ideally I would like to show a Bar and Line Chart. The Bar
illustrates Monthly Totals, the Line illustrates Running Total.

Fields Used:

TimeSheetDate, ProjectID, Hours

Desired results:

Month MoTot RunTot
Jan 100 100
Feb 200 300
Mar 150 450

Thanks

The expression for RunTot should be something like

=DSum("[MoTot]", "queryname", "[TimeSheetDate] <= #" & [TimeSheetDate] & "#")

assuming that TimeSheetDate is in fact a date/time field.

John W. Vinson [MVP]

 

[Guest]

Thanks for the reply John. See my reply to Al above. The problem is in
creating a query to populate a CHART with MTD and YTD data simultaneously. I
am assuming =DSum is for use in a report.

On Fri, 20 Apr 2007 12:52:05 -0700, Randy Brown <Randy

I am trying to represent Monthly and Overall Labor totals on a Chart. My
query calculates the monthly totals just fine. How do I calculate a running
total? Ideally I would like to show a Bar and Line Chart. The Bar
illustrates Monthly Totals, the Line illustrates Running Total.

Fields Used:

TimeSheetDate, ProjectID, Hours

Desired results:

Month MoTot RunTot
Jan 100 100
Feb 200 300
Mar 150 450

Thanks

The expression for RunTot should be something like

=DSum("[MoTot]", "queryname", "[TimeSheetDate] <= #" & [TimeSheetDate] & "#")

assuming that TimeSheetDate is in fact a date/time field.

John W. Vinson [MVP]

 

[John W. Vinson]

Thanks for the reply John. See my reply to Al above. The problem is in
creating a query to populate a CHART with MTD and YTD data simultaneously. I
am assuming =DSum is for use in a report.

You can base a Chart on a QUERY using the DSum() expression as a calculated
field.

John W. Vinson [MVP]

 

[Al Campagna]

Randy,
Your chart will have it's own query. Are you thinking that it has to use the same
query as behind the report? The report can be detail with sums, the chart could be just
summary in nature.

--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

Thanks for the reply John. See my reply to Al above. The problem is in
creating a query to populate a CHART with MTD and YTD data simultaneously. I
am assuming =DSum is for use in a report.

On Fri, 20 Apr 2007 12:52:05 -0700, Randy Brown <Randy

I am trying to represent Monthly and Overall Labor totals on a Chart. My
query calculates the monthly totals just fine. How do I calculate a running
total? Ideally I would like to show a Bar and Line Chart. The Bar
illustrates Monthly Totals, the Line illustrates Running Total.

Fields Used:

TimeSheetDate, ProjectID, Hours

Desired results:

Month MoTot RunTot
Jan 100 100
Feb 200 300
Mar 150 450

Thanks

The expression for RunTot should be something like

=DSum("[MoTot]", "queryname", "[TimeSheetDate] <= #" & [TimeSheetDate] & "#")

assuming that TimeSheetDate is in fact a date/time field.

John W. Vinson [MVP]

 

[Guest]

John, your solution worked great! THANKS! Now all I have to figure out how
to use DSUM for YTD when fiscal year starts in Oct.

Randy

 

[John W. Vinson]

John, your solution worked great! THANKS! Now all I have to figure out how
to use DSUM for YTD when fiscal year starts in Oct.

= DateSerial([FiscalYear] - 1, 10, 1) AND < DateSerial([FiscalYear], 10, 1)

or an equivalent expression.

John W. Vinson [MVP]