By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,456 Members | 1,505 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,456 IT Pros & Developers. It's quick & easy.

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

P: 6
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
Dec 20 '07 #1
Share this Question
Share on Google+
3 Replies


Denburt
Expert 100+
P: 1,356
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?
Dec 21 '07 #2

P: 6
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
Dec 22 '07 #3

Denburt
Expert 100+
P: 1,356
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
Dec 27 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.