Searching Multiple Tables In One Form

[RussOSU]

I am looking to create a search form which will be able to search fields from 2 different tables. I have an Employee table and a employee certification table. These tables are related in that the Employee ID (EID) is located in both tables. the Employee Cert table is composed of a 2 ID numbers one the EID and the other a certification ID (CertID). I am wanting to create a search form in which I can search by Name, Age, Status, City, Certification Type/Level. This form would then return a list of Employees Names and Cert which the names are hyperlinked to open the corresponding form with the employees information. I am unsure how to do this due to the cert being a seperate table. Any ideas or help is very much appreciated.

Russ

 

[OldTom]

Hi RussDogg

I have a similar structure in operation on a number of databases, and the general technique is build a dynamic sql string containing the search criteria. (I've assumed that you want to be able to select by any / all combinations of the fields you've mentioned)

This dynamic sql string can then be used either to create a query definition or to set as the Record Source for the form.

The largest application that I have uses any combination of 16 select criteria and generally retrieves from a main table of 35,000 entries within 1 to 2 seconds.

This is in a continuous form from which I link across to other forms/subforms and various hyperlinks.

If you want to send me a zip of your structure I'll have a look at it and see if I can throw some sql build in for you.

Tom
tommoran@btclick.com