One set of records from multiple tables

[BLutgen]

I have three tables. The fields in the tables are almost exactly the same, except for the field [model] since this is what differentiates the product into the different tables. I want to run a query that will search all three tables for a common name, like a distributor name. This way I can type in the distributor name and get all of the orders for that distributor no matter what type of product they ordered. I need all of the fields to show up on the results since the other fields are also order information. How would I set up this query? Be gentle on me, my company just threw this at me and I just started Access. Thanks a ton.

 

[tlbroadbent]

If all the tables are identical in structure the follwoing will work otherwise you'll need to name the corresponding columns in the same order each query Select list.

Select * From tblA
Where [Distributor Name] = "the name"
Union
Select * From tblB
Where [Distributor Name] = "the name"
Union
Select * From tblC
Where [Distributor Name] = "the name"

You may want to use Where [Distributor Name] Like "*the name*" to allow user to search on partial names.
Terry

http://members.home.net/tlbroadbent/prog.htm

------------------------------------
Experience is the hardest kind of teacher. It gives you the test first, and the lesson afterward.