Filter large data set

  • Thread starter Thread starter Thomas Kroljic
  • Start date Start date
T

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,
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
 
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
 
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
 
Back
Top