Lypaap
asked on
Access 97 Calculating both Month and YTD totals on a report
I need to generate both YTD and Monthly totals on a report.
YTD - Works fine: Need Totals For Clients, Other Hours, Counseling Hours and Total Hours.
I Calculate The Year To date As Follows:
Calculate Clients
=Count([Fname])
Calculate Other Hours (Textbox39)
=Sum(IIF([Activity]="other
Calculate Total Hours (Textbox41)
=Sum([Hours])
Calculate Counseling Hours
= [Textbox41]-[Textbox39]
Now I Need to Calculate The Current Month for:
Clients, Other Hours, Total Hours and Counseling Hours
I need to setup a varriable that I can change each month
for the current month I want on the report. In the format
MM/YYYY. I have a field in each record called Fmonth in the same format MM/YYYY.
In a nut shell I need the following:
Calculate Clients If the varriable = the Fmonth I want to count Fname like I do above in the year todate section above
Calculate Other Hours If the varriable = Fmonth I want to Sum Hours like I do above in the year to date section.
Calculate Total Hours If the Varriable = Fmonth I want to sum Hours like I do above in the year to date section
If I can get the above three totals I can get the forth total
Counseling Hours by Subtracting Other Hours From Total Hours.
I can't seem to setup the varriable or work with Fmonth in a calculation.
Any suggestions
AL
YTD - Works fine: Need Totals For Clients, Other Hours, Counseling Hours and Total Hours.
I Calculate The Year To date As Follows:
Calculate Clients
=Count([Fname])
Calculate Other Hours (Textbox39)
=Sum(IIF([Activity]="other
Calculate Total Hours (Textbox41)
=Sum([Hours])
Calculate Counseling Hours
= [Textbox41]-[Textbox39]
Now I Need to Calculate The Current Month for:
Clients, Other Hours, Total Hours and Counseling Hours
I need to setup a varriable that I can change each month
for the current month I want on the report. In the format
MM/YYYY. I have a field in each record called Fmonth in the same format MM/YYYY.
In a nut shell I need the following:
Calculate Clients If the varriable = the Fmonth I want to count Fname like I do above in the year todate section above
Calculate Other Hours If the varriable = Fmonth I want to Sum Hours like I do above in the year to date section.
Calculate Total Hours If the Varriable = Fmonth I want to sum Hours like I do above in the year to date section
If I can get the above three totals I can get the forth total
Counseling Hours by Subtracting Other Hours From Total Hours.
I can't seem to setup the varriable or work with Fmonth in a calculation.
Any suggestions
AL
ASKER
Hi Jadedata (Jack)
I looked at the grouping capability and can't understand how to make it work.
Here is what I am trying to do:
Print a report grouped by Counselor Name. showing totals for each counselor for a given month and also year to date. Totals
are for Number of Clients, Counseling Hours, Other Hours and Total Hours.
Example:
Our Fiscal year is from Oct 2003 through Sept 2004
I need to tell the program to give me the above four totals for
the month of November 2003 (For current month) also give
me the four totals for October and November as year todate.
Next month we would ask for the month of December and year Todate For October November And December
Report would look like this:
Month
John Doe Clients Coun-Hours Other-Hours Total Hours
Sam Jones Clients Coun-Hours Other-Hours Total Hours
Off to the right show Year Todate Same Totals.
Everything on the report are totals summed from hundreds of
records. All detail is suppressed.
Hope this explains it.
Best Regards
AL
I looked at the grouping capability and can't understand how to make it work.
Here is what I am trying to do:
Print a report grouped by Counselor Name. showing totals for each counselor for a given month and also year to date. Totals
are for Number of Clients, Counseling Hours, Other Hours and Total Hours.
Example:
Our Fiscal year is from Oct 2003 through Sept 2004
I need to tell the program to give me the above four totals for
the month of November 2003 (For current month) also give
me the four totals for October and November as year todate.
Next month we would ask for the month of December and year Todate For October November And December
Report would look like this:
Month
John Doe Clients Coun-Hours Other-Hours Total Hours
Sam Jones Clients Coun-Hours Other-Hours Total Hours
Off to the right show Year Todate Same Totals.
Everything on the report are totals summed from hundreds of
records. All detail is suppressed.
Hope this explains it.
Best Regards
AL
If you do not have a field in the table that designates the Fiscal Year assignment, you query can produce one by "offsetting" the date of a record to push it into the appropriate Fiscal Year. This would become one of your report groups, or at least a criteria in the query that feeds the report.
You will also need to create two separate queries because the aggregate groups are different between the Month and the year to date.
For a YTD you will need yet another query to compute these values
You now need a "Cap" for all three queries, pulling in the totals for each period (all groups by employee, your linking field...)
and use this Cap Query as the recordsource for the report.
In building the queries, make your life easier.. Use names for the columns that indicate which period and total the column represents.
ie Yr_Clients.
Refrain from using spaces, dashes(hyphens) in the column names. Use underbars (_) to separate phrasing.
Be careful how complex you try and make the report. You have to live with the consequences of changes later..
You will also need to create two separate queries because the aggregate groups are different between the Month and the year to date.
For a YTD you will need yet another query to compute these values
You now need a "Cap" for all three queries, pulling in the totals for each period (all groups by employee, your linking field...)
and use this Cap Query as the recordsource for the report.
In building the queries, make your life easier.. Use names for the columns that indicate which period and total the column represents.
ie Yr_Clients.
Refrain from using spaces, dashes(hyphens) in the column names. Use underbars (_) to separate phrasing.
Be careful how complex you try and make the report. You have to live with the consequences of changes later..
ASKER
Hi Jadedata (Jack)
Thanks for all your comments, however its pretty much all over myhead.
I think I will spend a little more time trying to come up with a formula to calculate the totals.
I do have a field in each record that identifies the month and year.( Fmonth) So some how I need to write the formula the
same way I did for Year Todate above, except only do it when Fmonth is equal to a specific month. I am not cracy about changing the month in the formula each month to get the monthly totals, however we will do that if necessary.
My only other alternative is to print to seperate reports, one with the year todate totals and one with the monthly totals. I can do
this just fine, however management isn't happy with that
approach.
Thanks for all your help I hope I havn't wasted to much of your time.
Best regards
AL
Thanks for all your comments, however its pretty much all over myhead.
I think I will spend a little more time trying to come up with a formula to calculate the totals.
I do have a field in each record that identifies the month and year.( Fmonth) So some how I need to write the formula the
same way I did for Year Todate above, except only do it when Fmonth is equal to a specific month. I am not cracy about changing the month in the formula each month to get the monthly totals, however we will do that if necessary.
My only other alternative is to print to seperate reports, one with the year todate totals and one with the monthly totals. I can do
this just fine, however management isn't happy with that
approach.
Thanks for all your help I hope I havn't wasted to much of your time.
Best regards
AL
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi jadedata (Jack)
Thanks for all your help. My problem is solved.
Best Regards
AL
Thanks for all your help. My problem is solved.
Best Regards
AL
Most Excellent!
You could be using the built in grouping capability of a report format to do this for you.
Is there some reason I don't see that as part of your question??
In a report format there is no need to store these to variables, it's handled by the report.
regards
Jack