How to get daily, MTD, YTD info on same report

[Guest]

I have to make a report each day that lists our production parameters for the
previous day, month-to-date, and year -to-date on the same report. To make
it even more difficult, these are listed next to each other. An example is
as follows:

Production Yesterday MTD YTD
Actual 23,000 149,342 1,235,674
Goal 25,000 165,500 1,350,750

To complicate matters, I have to take different "cuts" at the same data.
For example; I list overall production, production by shift, production by
line, and product type. I've tried a "brute force" method of doing separate
queries for daily, MTD, and YTD. I then combined them into a single query to
go into a report. Unfortunately, it was too big for Access. After
streamlining best I could, I then got "too complex" error... Thanks for any
advice!

 

[Guest]

Try making separate queries for each total you need. Then make a report query
comprised of these.

This is just a SWAG as we don't know your table structures, if its all
Access or hitting another type of DB, your network traffic, etc. This
information would allow those of us reading the post to make more accurate
suggestions.

Hope this helps

John Cello
www.johncelloconsulting.com

 

[david epsom dot com dot au]

Another way to do it is to create a series of flags on each line,
indicating which sums the record will be included in:

bYearToDate: (dt > #2004/12/31#),
bMTD: (dt >= dateserial(Year(date),month(date),1)
bYesterday: dt = date-1

Then to do the sums on your report, bind controls to calculated
controls like this:
=-sum(amount * bMTD)

If it too complicated even for that, create counter functions
in VBA instead of expressions. You can bind the counter functions
to controls in an invisible details section. (Counter functions
work better in reports than in queries, because it is hard to
control how often a function gets called from a query).

(david)