There are currently 1246 records. And it will be double that within a year.

tSample had an index only for Key - the PK. I added a muli-field index
using CNTYNAMe and ID.

tReview has an index for ReviewKey - the PK and for Key - the FK.

I added the multi-field key because I read that any field that is used to
sort should have an index. Should I remove it?

Thanks much.

"Jerry Whittle" wrote:

I doubt that indexes will help much. How many records are we talking about?
If less than a thousand, indexes usually don't help anyway.

In fact you might have too many indexes already. Access automatically
creates indexes for fields with ID; key; code; num as part of the name. I
noticed that you have ReviewKey (PK) and Key (PK). If you check you might
find that there are at least 2 indexes on these fields: one for the PK and
the other by just having Key in the field name.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"jmoore" wrote:

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.