Query Multiple Tables, return results from the one table it value is found in

[MattioMatt]

Hello all,

Is it possible to to use a query to search for a value in a text box on a form and look it up in 3 different tables and return the results from the one table it was found in?

I currently have a database to hold information on assets. I have an assets table and each area of the business provides a list of assets and information relating to those assets. Each list has some columns named the same others not.
The column names that are consistent is IP address and asset name.

I'm looking to have a form with a text box for either the IP address or asset name and look it up in the 3 of the tables until it finds it then returns the results along with the different information associated with it in that list.

Below is an example as some of the asset lists have many many columns, the point being I can't merge them using an append query as there is differences in the columns.

tblAssets
IP | IP Address

tblAssetList_A
IP | Asset Name | IP Status | Vendor

tblAssetList_B
IP | Asset Name | OS | Asset Priority

tblAssetList_C
IP | Asset Name | OS | AssetStatus |

 

[Ranman256]

you CAN make a union query.
pad the smaller tables with blank fields so all queries have the same #.
then run 1 union query.

Q1, select IP, ipAddr, fld3:"",fld4:"", Tbl: "tblAssets" from tblAssets where [field]=forms!myForm!txtBox

Q2 , select IP, AddrName, Vendor, fld4:"", Tbl: 'lstA" from tblAssetList_A where [field]=forms!myForm!txtBox

etc

union query:
select * from Q1
union
select * from Q2
union
etc

 

[MattioMatt]

Hi Ranman256,

Thank you so much for your reply and input on this. I've read your reply a few times and I feel it makes sense.

Query each list and use a union query to bring those together, that way it will look up the search value across all of the tables.

When you say to pad out the smaller tables, could you clarify that point - not sure I quite understand that bit.

I can use this on another query for the asset vulnerabilities list. However in these lists (separate lists against just as before) the asset could be listed many times depending on the amount of vulnerabilities found. Similar issue to the above these have different amounts of columns and names.
I was going to use continuous forms to display the vulnerabilities based on the asset searched for.
My next question is if I use the above example you gave is there a way I can get the continuous form to open and return the columns from that table.

For example:

Search for the asset name on the search form. Click on a link to open a pop up continuous form that displays all of the vulnerabilities associated with that asset but display the column names depending on the table it found the value in.
I'm presuming this would need to be done with having a continuous form and the fields set for each of the tables then using VBA to determine which form to open unless I'm not understanding how that would work correctly?

 

[MattioMatt]

I've fully understood the union part of the query and in addition the padding out of the cells now after some time playing around with it.

I've also go around having a different form with the different columns by using the padding and then adding an expression to state which source table it is from.

Thanks so much for your help.