Crazy Monthly Report - backdated solutions

[skeyes]

Hello!

I have a report that I generate each month that collects a wide variety
of statistical information from about 100 different queries. This
report used to be manually calculated in Excel, until through my
diligent efforts I created all these queries and subreports. This
report is generated on a monthly basis, and I made many queries
dependent on a text box containing a date which can be altered in a
related form to calculate for the new month.

In my programming, I tried to make it such that you could type in a
date in the past month on the form and it would spit out the old report
for that month, but now I realize that this will not work because some
of the data gets overwritten each new month.

This report is only one page front and back, but it contains so many
subreports and complex union/crosstab queries etc. behind it, that I
have to break it down into chunks. The front page I have to run
through the printer in two chunks, and then flip it over and print the
back. However, this is not important.

What I would like to do is make some sort of code to calculate each
month and store the information in a large table for each respective
month, and then I could just generate the report directly from that
table and save a lot of hassle. Is there a simple way to update a
large number of fields in a table from various complex queries WITHOUT
making a ton of update queries / make table queries? I am confident
that I could do it with queries, but there has to be a better way
through VBA or something. All help would be greatly appreciated.

 

[Marshall Barton]

I have a report that I generate each month that collects a wide variety
of statistical information from about 100 different queries. This
report used to be manually calculated in Excel, until through my
diligent efforts I created all these queries and subreports. This
report is generated on a monthly basis, and I made many queries
dependent on a text box containing a date which can be altered in a
related form to calculate for the new month.

In my programming, I tried to make it such that you could type in a
date in the past month on the form and it would spit out the old report
for that month, but now I realize that this will not work because some
of the data gets overwritten each new month.

This report is only one page front and back, but it contains so many
subreports and complex union/crosstab queries etc. behind it, that I
have to break it down into chunks. The front page I have to run
through the printer in two chunks, and then flip it over and print the
back. However, this is not important.

What I would like to do is make some sort of code to calculate each
month and store the information in a large table for each respective
month, and then I could just generate the report directly from that
table and save a lot of hassle. Is there a simple way to update a
large number of fields in a table from various complex queries WITHOUT
making a ton of update queries / make table queries? I am confident
that I could do it with queries, but there has to be a better way
through VBA or something.

The only other way I can think of is to use code in the
report to open a recordset for each table and write the data
in each section's Print event. Be sure to check for the
data having been written previously because report sections
can be processed multiple time in a semi random order.

A different approach that makes more sense to me is to
forget the tables and send the report to a snapshot or PDF
file. Then, if anyone want to see historical data, they can
just print the file.