Complex Report

[Garridon]

For my report, I need to display statistics from the last three months. The report needs to count the number of suspenses for the each month, calculate the suspenses that were overdue for that month, and display percentage overdue. There are seven departments, and I have to do one of these reports for each one. The best solution I've been able to come up with is three separate queries (one for each month), one report, and two subreports per department. Is there an easier way to do this?

Thanks!


Linda Adams
Visit my web site for writing and Microsoft Word tips:

http://www.hackman-adams.com

Official web site for actor David Hedison:

http://www.david-hedison.com

 

[JerryDennison]

This can probably be done with a single totals query and grouping levels.

 

[Garridon]

I thought about that, but how do I get it to print only the current three months (not the last 90 days)? And, the thing that's really stumping me--how do I count the overdue records for each month?

Linda Adams
Visit my web site for writing and Microsoft Word tips:

http://www.hackman-adams.com

Official web site for actor David Hedison:

http://www.david-hedison.com

 

[JerryDennison]

Yes, you can limit your records to only the last 3 months and everything else you're looking for. To limit to the last 3 months use the DateDiff() or DateAdd() functions. You can find syntax and usage under help. To group by month use the Format() function. To determine what is overdue you can use one of several methods to determine whether or not the date is beyond the due date or beyond a specific number of days or months past the origination date. However you are determining it manually can be done automatically. Again, probably using either DateDiff() or DateAdd().