Speeding up reports

G

Guest

This is going to be a long one so here I go.

My database is based on three cross-tab tables that are brought together
with a very big UNION query.

The UNION query is the basis for a calulated value (CHU) and this calculated
value has to also be an accumulating total (ACCUM CHU).

I do this by having two indentical queries (CHU1 and CHU2) calculate the CHU
values, then I have a query that uses an INNER JOIN function and the two CHU
queries to come up with an ACCUM CHU value. This query, by the way, is
called ACCUM CHU. This query also has a parameter limting the results from
40 station to one.

I then have a final query based on ACCUM CHU that furthers limits the
results to a date range I want to view. This query is called REPORT QUERY.

If I run REPORT QUERY by itself it is fairly quick in coming up with the
results, there is no problem here I can see.

I have a report that displays all the data. It is grouped by data-by-month.
In the Data Header I display the Station, month and two subreports. There
is nothing in the details section. One subreport shows a table of data for
the month and the other subreport shows a chart based on the same data.

I use a form to enter the station I want and the date range, and the queries
refer to this form for their parameters.

The problems comes when I run the report. It is VERY slow. I think that
the report and subreports are running the REPORT QUERY over and over again.
In fact I sure because before I started using the form, I had to enter the
same parameters up to five times to view the first page of the report, and
then again to view different pages of the same report.

I did a test by making the REPORT QUERY make a table by the same name and I
then ran the report. This time is was very quick. I could go forward and
backward through the pages of the report quickly.

So my question is...

Is there a way to make it so that the REPORT Query is ran only once and the
report and subreports use the same results over and over again,

Or

If this can't be done, can I have a query make a new table (I know how to do
this), have the report use this table (Also know how to do this), and then
have the query automatically delete this table (don't know have to do this)?

One more observation. In the making of this database, The database file
ballooned to over 100 mb! I did a compact and repair and this reduced its
size to 940 kb. Is there a way to avoid this ballooning effect or run
compact and repair automatically?

Thanks for any responses.

Jared.
 
M

[MVP] S.Clark

A make table query will make a new table of the results. I recommend that
you use an Append query instead, such that you can write the data to a known
table structure every time. (This will require a delete query to empty the
table prior to the append.)

For future query questions, try the m.p.a.Queries newsgroup.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top