Searching across multiple tables with same columns....

[ds9686]

Ok,

I have three tables that are named different, but have the same column names, but different data in them. The data in each table is linked by a master table that has a primary key.


What I need is a way to make a form with a box where I can search on a value that could be in any of the three tables and then the associted information from the site will display in the above boxes.


(Site Master Table contains all site information and the primary key)
(The three tables are sales order information. Stage Only table, Install table, and srop ship table)

Any ideas?

 

[seth_belgium]

Create a UNION-query (SQL-Specific) and use that to search in.

To create a union-query:

SELECT field1, field2, field3 FROM table1
UNION
SELECT field1, field2, field3 FROM table2
UNION
SELECT field1, field2, field3 FROM table3;

Note: Fieldnames don't nescessarily need to be identical in all three tables.

Greetz,

Seth