YTD vs Current Period reporting

[KerryL]

I'm a bit unsure of how to create the output required for a summary report. The database tracks notices, and the report is based on the following objects on frmRptCriteria:

txtBeginDate
txtEndDate
txtYear


Summary data the report needs to display:

Total notices YTD
Total notices this period (begin/end date)
Total notices entered during same period last year
No of Notices related to mass layoffs YTD
No of Notices related to mass layoffs this period
No of Notices related to plant closings YTD
No of Notices related to plant closings this period
Total database entries YTD
Total database entries this period
Total database entries during same period last year


I've created reports where the output is a result of a filtered query, but I don't understand how to pull filtered quantities out of the table (or full query) for the report. Once I get going I think I'll be fine, but I need some help getting started.

Any help that an expert can provide will be greatly appreciated. Thank you.

KerryL

 

[dhookom]

You can use multiple subreports or multiple queries joined together or code in the report or other possible solutions. You can count occurances against a criteria in a query with expressions like:
ColumnName: Sum(Abs(--- your expression ---))
for instance:
ThisPeriodCount: Sum(Abs([NoticeDate] BETWEEN Forms!frmRptCriteria!txtBeginDate AND Forms!frmRptCriteria!txtBeginDate))

All you have to do is figure out all the --- your expression --- criteria that return either True/-1 or False/0. If you sum the absolute value of the expression, you are counting the number of records that return true.



Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]