What Is The Best Way To Search For Records?

[bogushs]

Hi there


This may seem to be a very general question for some but im wanting to find out what the best possible way of filtering records?
I have a database that structurally is split up into 2 tables. The one table refers to primary address information and the other table with the more detailed table. These two tables are linked as one-to-many. Theres also about 20,000 records to my db.

When i use the advanced sort filter in Access my address table is the primary table im allowed to search by, not any of the secondary table fields.

Is there a way around this or should i be using other recordset or filtering techniques?

Does anyone know the code to use the advanced sort filter function, rather than going into the records tab in access?

Any comments to put me in the right direction greatly appreciated

cheers

 

[The_Doc_Man]

Issues that relate to record searches are as follows:

1. When you can search based on an index, that is always better. However, if the index is too big, that very quickly kills performance.

2. When you can search on shorter fields, you are better off than if you search on longer fields.

3. The question of "filtering" is sooooo wide open that it is almost impossible to answer without asking several questions in return.

For instance: Are your filters dynamic or static? It the searching process always based on the same fields? Are you searching on whole fields, initial subsets, or arbitrary subsets? Have you reduced your tables to the minimum size for normalization considerations?

Until you can answer those questions, we would have one helluva time trying to give you more than very general answers.

Let me say this about searching: If there ARE any things you can to do pre-categorize your searches, do so. Then either store the category code in the table and search that.... (not preferred but barely acceptable) OR be sure that you have a viable prime key (perhaps autonumber) on the main table, then build a separate table with the pre-categorized codes that have the main table's prime key as their foreign key. Then search the category table and join it to the main table to get the rest of the records.

It is fair game to have a pre-defined search table for every possible class of pre-categorization because these tables will likely be small. You want to avoid doing messy searches by doing as much of your categorization ahead of time.

Don't know if that helps or hinders, but it is one man's thoughts on the subject.