Multiple Subreports - huge database

  • Thread starter Thread starter Paige
  • Start date Start date
P

Paige

I have a database with 600,000 records. I need to create
a summary report that summarizes coded fields and breaks
them down by their values by quarter. I created queries
for each of the values but I am having to make subreports
to get the values to print by quarter. The problem is
that I am up to 8 subreports already and it is taking
hours to run. Is there a better way to summarize the
data by value without having to pull from 7 queries?
Would the DLOOKUP/DSUM functions be helpful here?

Here is an example of what I need to do:
Hospital A Q1 Q2 Q3 Q4
Discharges
Gender (code F or M)
Male 3 3 2 1
Female 5 3 1 4
Admit Source (Codes 1-6)
ER 3 2 2 1
Inpatient 2 4 6 1
Referral 3 2 1 2
Self 1 2 3 4
Discharge Status (codes 10 thru 60)
Discharged Home 2 2 3 5
Deceased 1 2 5 3
Transferred 3 4 5 6
Charges ($$)
Op Room $400 $500 $600 $700
Lab $333 $322 $567 $987
..
..
..

I have about 10 more categories I need to include - each
category can have a number of values!

Any help is appreciated. I am currently just running the
report for 2 hospitals and it takes over 10 minutes to
run and then additional time to format the report pages!
Help!

Paige Clements
 
One way:

nQ1: iif(([mydate] > #1/1/1111) and ([mydate]<=#2/2/2222#),1,0)
nQ2: iif(([mydate] > #2/2/2222) and ([mydate]<=#3/3/3333#),1,0)
nQ3: iif(([mydate] > #3/3/3333) and ([mydate]<=#4/4/4444#),1,0)
nQ4: iif(([mydate] > #4/4/4444) and ([mydate]<=#5/5/5555#),1,0)

It is almost never necessary to use sub reports to get grouping
and summation. Even DSUM would probably be faster, because
almost anything would be faster than a report that takes
hours to run. I've got complex, slow summation reports,
using data out of union queries out of multiple databases
on the network, with calculated values and VB code operations
instead of query SQL filtering on the records, and they still
only take seconds on a good PC.

For comparison, with current hardware, I don't have any report
that takes much longer to run than twice the time to copy the
whole database off the network. And If I drop the union queries
and the page numbers the slow reports are about 4 times faster.

Presumably, (if you don't have many pages) what is killing you
is the time taken to sort each of the recordsets you need to
group. To make your report run faster, you need to make sure
that the recordset is sorted only once, even if that means
creating calculated values in the query.

Getting rid of the subreports would also have the advantage
of making the page formatting faster.

(david)
 
Back
Top