Help with using a subquery to filter on multiple tables using a single form

[jcf378]

Hi all--
Does anyone have any insight as to how I might create a search form that allows a user to select criteria based on any related table in the whole database. The search form I have now only allows me to filter based on variables in a single table. I would like to have a search form where I can select multiple variables (from various linked tables) to filter by, and return results based on this multi-table filter.

Allen Browne has a wonderful description of how to create a simple search form: <http://www.allenbrowne.com/ser-62.html>, and he even alludes to a way "to filter on other tables that are not even in the form's RecordSource, use a subquery" -- on his subquery page (<http://www.allenbrowne.com/subquery-01.html#Search>), he describes using subqueries in the context of his simple search form to create a search form "where the user can select criteria based on any related table in the whole database". I was wondering if anyone had some thoughts as to how I might actually code something like that.

Thanks,
-Jason

[Denburt]

Maybe you could supply a listbox that lists all the tables in the DB then choose a table to search by. Once the table is chosen you can use that table name in the search query.

Another possible method would be to create a union query containing the field you want to search from each of the tables you want searched, from there you can use sourcetable or something to retrieve the table name.

Does this help?

[jcf378]

thanks for your input...i'm still fuzzy having trouble figuring out how I would display results on a single page that were inclusive of all the fields (across the various tables) that were searched...

--jason

[Denburt]

Back from the holidays. If the tables contain contain the same number of fields containing the same data such as multiple archived tables or something then I would use a Union query for what you are asking. then you can base a form or subform on these results.

Select field1, field2 from Table1
Union Select field1, field2 from Table2