Filter large data set

[Thomas Kroljic]

All,
I have a table with 14,000 contact records. I have a form that the
endusers use to view their contacts. This form allows them to "filter" their
dataset. At anytime, the user can double-click on a record and go to a form
displaying the full record for the selected contact. This form, the 2nd
form, also allows the user to scroll forward and backward through their data
without having to go back to the first form. When the user "double-clicks"
on a contact on the first form, I use vba to assign the recordsource from
form 1 to form 2. I then apply the same filter parameters to the second
form.

Problem: as more and more contacts (records) are added to the table,
the process of opening up the 2nd form is becoming slower and slower. Does
anyone have any suggestion on how I can speed this process, opening the 2nd
form, up? Is there a way to assigned the filtered recordset to the second
form without having to apply a filter to the 2nd form?

Thank you,
thomas j. kroljic

 

[Arvin Meyer]

Try adding indexes to the fields you are filtering by. Indexes can often
speed up a query by a factor of 10. You should be able to get 10 records out
of a million in under 2 seconds.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access Downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Thomas Kroljic]

Arvin,
Thank you very much for responding so quickly. As for indexes, if I give
the user the ability to filter on any combination of 15 different fields,
how would I go about creating the appropriate indexes? Does it pay to have a
single component index for each field? If so, that would mean creating 15
single component indexes. And there would be way too many combinations of
the 15 fields to even begin to try and create multiple field indexes.
I do have indexes on the table, but they cover the major field names like:
contact_id and last_name...

Should I still go with the single component indexes on each available filter
field?

Thank you,
Thomas J. Kroljic

 

[Arvin Meyer]

You will have to do some experimentation to get the optimum setup. Do you
really think that they might need to filter on 15 different fields? If so, I
would try first with 15 separate single field indexes. I can't think of a
situation, though where I'd want to filter on more than 5 or 6 fields (at
most).
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access Downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Thomas Kroljic]

Arvin,
You have a good point. I'll ask some of the users what filters they use
the most. This should give me a good feel for what indexes I need to create.

Thanks,
Thomas J. Kroljic