How do I create a select YTD query that totals monthly summaries?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have joined my monthly queries and created the following type of expression
for each field however this query has become cumbersome and stopped returning
accurate totals. How can I calculate YTD totals from monthly summarized data
in a more efficient manner?
 
I forgot to include my Example expression in my orignial post:



SumofReceipts: nz([SUMMARY PIRDETJAN07]![SumOfReceipts],0)+nz([SUMMARY
PIRDETFEB07]![SUMOFRECEIPTS],0).

My goal is to sum all monthly totals into YTD total. Thanks
 
I have joined my monthly queries and created the following type of expression
for each field however this query has become cumbersome and stopped returning
accurate totals. How can I calculate YTD totals from monthly summarized data
in a more efficient manner?
Click to expand...

What's your underlying table? Generating a dozen monthly queries and then
trying to reassemble them is almost certainly not going to be your best bet...

I would suggest creating a Totals query on the raw data, grouping by the month
(e.g. a calculated field, TheMonth: Format([datefield], "yyyymm") ); base a
Report on the query and put controls in the report footer summing the values
in the detail section. Not knowing anything about the nature of the data being
summed or your existing queries, though, it's hard to be specific!

John W. Vinson [MVP]
 
Hi John,
My monthly data comes from a system report I download into access
that has 2000-3000 lines of data which I summarize into a maximum of 31
totals. I then link the summarized monthly totals to a location names table
and try to summarize again for YTD which should be a simple 2 step operation.
I am wondering if there is a better approach than repeating the above
expression 12 times for each desired YTD subtotal (one for each monthly
report summary). How might I accomplish that more efficiently? I asked for a
YTD system report but you know the answer I got to that! =:o) Thank you for
your help.

John W. Vinson said:
I have joined my monthly queries and created the following type of expression
for each field however this query has become cumbersome and stopped returning
accurate totals. How can I calculate YTD totals from monthly summarized data
in a more efficient manner?
Click to expand...

What's your underlying table? Generating a dozen monthly queries and then
trying to reassemble them is almost certainly not going to be your best bet...

I would suggest creating a Totals query on the raw data, grouping by the month
(e.g. a calculated field, TheMonth: Format([datefield], "yyyymm") ); base a
Report on the query and put controls in the report footer summing the values
in the detail section. Not knowing anything about the nature of the data being
summed or your existing queries, though, it's hard to be specific!

John W. Vinson [MVP]
Click to expand...
 
to eksheare: your sample code just saved me - I was summing three fields, but
did not include the NZ function. THANK YOU!!!!
 
Back
Top