469,625 Members | 1,714 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,625 developers. It's quick & easy.

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

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.

Dec 20 '07 #1
3 2462
1,356 Expert 1GB
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
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...

Dec 22 '07 #3
1,356 Expert 1GB
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.

Similar topics

5 posts views Thread by Steve Patrick | last post: by
7 posts views Thread by Jeff | last post: by
reply views Thread by debasisdas | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.