Report with YTD and current month

[Prufrock]

Hi,

I have a report which is activated by using a form which allows the user to enter start and end dates. This criteria is then placed in a query and then a report is generated. This works well for giving us the current month totals for the records.
I also want a YTD total that goes from the first of the year and ends with the end date put in the form.
How can I get both on the one report?

e.g.

Number of Clients Current Month YTD
Weaving
Swordsmanship
Ice Skating

Thanks

 

[scriverb]

You can use the DSum function for each row of the Detail section. DSum using the Date of the beginning of the year/fiscal year(i.e. 1/1/2004 or 10/1/2003 etc.) thru the end date from the form.

Are you using a query to rollup the current month records. Is there a query that is the recordsource for the report? If so post the query SQL here and I will demo the use of the function.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[dhookom]

I would create a separate query.
SELECT Sum(Abs(Year(DateField) = Year(Date()))) As YTD,
Sum(Abs(Format(DateField,"yyyymm") = Format(Date(),"yyyymm"))) As MTD
FROM YOURTABLE;

This will create a single record with YTD and MTD counts of records. Add this to the report's recordsource with no joins to place these values anywhere in the report.

If you need something other than counts, you should provide the specifications.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[Prufrock]

Thanks Bob

Here is the SQL for the query. I am aware what you mean about DSum but I need to know how to get the "cbomonthend" date which is what the user inputs for the query, into the expression to ensure it only sums from the first of the year through to the date that is the end date in the form.

Much appreciated.

SELECT tblMonthly_Report_Statistics.StaffMember, tblMonthly_Report_Statistics.Date, tblMonthly_Report_Statistics.ProgramTitle, tblMonthly_Report_Statistics.NumberofGroupsDelivered, tblMonthly_Report_Statistics.SessionsDelivered, tblMonthly_Report_Statistics.PrisonersCommencing, tblMonthly_Report_Statistics.PrisonersCompleting, tblMonthly_Report_Statistics.AssessmentsCompleted, tblMonthly_Report_Statistics.AssessmentContactHours
FROM tblMonthly_Report_Statistics
WHERE (((tblMonthly_Report_Statistics.Date) Between [Forms]![frmMonth_Ending]![cbomonthstart] And [Forms]![frmMonth_Ending]![cbomonthend]));

 

[dhookom]

Prufrock,
Are you looking for one figure each for YTD and MTD or totals by StaffMember, ProgramTitle,...?

DSum()s are terribly slow and inefficient.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[scriverb]

Prufrock: dhookup is correct in that we do need to know what category we need to be grouping by. Also, just what are we totallying? The number of staff members? Just what do you mean to by Totals. Totals of what? Please clarify.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[Prufrock]

Sorry Guys.
My dilemma is I have to mirror a paper report they have collolated for years. Problem is it has each program they deliver, but different fields apply to different programs, hence DSum allowing some flexibility what to include for some but not others.

It is summing the total of certain fields for certain programs like Assessments may only have Assessments completed for the month and total contact hours for the month.

Then there is to be a similar total but for YTD.

I have it working smoothly after all the work with sums running across the monthly and Dsums across the YTD but to make for smooth operation I need to get the end date they type in the form into each dsum for YTD. Can this be done?

My other thought was to run two queries one where the dates go straight in for the month in date criteria, another where the first of the year is set in criteria and the form inputs the end date. But then the dilemma is how to link them to the report and utilise the combined queries in there.

Thanks for your patience guys.

 

[scriverb]

In answering your two problems:

1. To use the forms EndDate textbox in the DSum function you must refer to in in the criteria expression in the following format: FORMS!formname.controlname

DSum("[[i]fieldname[/i]]", "[[i]tablename[/i]]", "[[i]datefieldname[/i]] = #" & FORMS![I]formname.controlname[/i] & "#;")

2. The two queries idea will work also. As dhookum stated just create the queries and then take them into your final query with any links added between the tables. The fields from these queries are now avaialble to be displayed and used on all of the rows.

Post back if you have further questions.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]