Query using single parameter to search multiple tables in same database?

[kryptkitten]

Hi All,

My guess is there is probably a relatively easy way to do this, but not sure how to approach it. I have a database of multiple tables that I need to be able to search based on a single parameter entry that is entered only once and for the query to return any records that equal that parameter.

So for example I have 5 tables and each of them has a field called AssociatedDocuments and I need a query that will search each of those 5 tables in the AssociatedDocuments field for the parameter value with only having to enter the parameter value once. Let's say the value is EQ.305. I need the query to search all 5 tables for EQ.305 and return any records from those 5 tables that are associated with EQ.305.

Thanks in advance!

Stacy

 

[PaulO]

Yes, it can be done, running a Macro containing five Queries all linked to the same Text Field on a dedicated Form. The key question appears to be ... How are you wanting to view/display the 'matching' records?

Your options are:
1) In a single table built by your Macro with its five Queries with key selected fields from each Table
2) In five separate pop-up Select Queries showing all fields for all matching records

 

[kryptkitten]

Sorry for the delayed reply I have been ill.

Option 1 seems to be the route that makes the most sense.

It's been a very long time since I have written a macro so would you be able to provide some example code for me to try and work from?

Thanks!

Stacy