Thanks for your offer. I posted the SQL for the queries in the queries
group. Each field in tReview is a rating of that criteria for a sample set of
cases from tSample. There are a large number of cases in tSample that will
not be used. Each case in tReview is a row, and contains all of the
information for the complete review. A report for each county with a summary
of their cases is produced during the year. These work fine. At the end of
the year, an annual aggregate report is needed. It gives the information we
need, but is very slow. I realize this is designed like a spreadsheet, but
this has been in use for a year and I don't know how to change it, or
transfer all of the data to a new design. I hope this is enough detail. I
appreciate any advice you have.

"Steve" wrote:

You can significantly improve performance by a redesign of your tables.
Without knowing anything about your database, I can tell you the design of
tReview is very wrong just looking at what you posted. Your query(s) have
got to be very complex hust to overcome the misdesign of tReview! Please
give a detailed description of what you are trying to do in your database,
and I will be very glad to give you a suggested redesign of your tables.

Steve




"jmoore" wrote in message
...

After reading several posts here I have changed all of the lookup fields
to
text fields in my table and deleted the relationships to the lookup
tables.
I came to this area looking for a solution to slow response time. Again,
after reading several posts, I suspect it is due to the database design,
but
it is what I have to work with.

Other than the lookup tables, there are only two related tables. If it
would be helpful, I can remove the lookup tables.
tSample (with the major fields listed below)
Key (primary key)
CNTYNAME
ID
Strata
Sample

tReview
ReviewKey (PK)
Key (FK)
A1
A2. . .
B1
B2. . .(in total there are over 60 number fields and 50 memo fields).

I read that creating indexes will increase response time. Will creating a
unique index from two fields in addition to the PK increase response time
(e.g., in tSample, a unique key comprised of CNTYNAME and ID)?

All was going well for the individual county reports, but I had problems
when an aggregate report was needed at year end. I was able to solve the
problem with advice obtained through this site. First, I created a union
query (qUnion); a crosstab query based on the union query
(qUnionCrosstab);
and combined qUnionCrosstab and qReviewCountCrosstab into one query
(qCrosstabsCombined) for the aggregate report. The aggregate report is
extremely slow to open and painfully slow in design view.

Will it help to create additional indexes? If not, I can live with this
report as it is since I only need it once a year. I don't know how to
change
the design and transfer the data.

Thanks for any tips for improvement. I am very grateful for the great
information I have found here.