SQL Query

[_Otacustes_]

Hello Guys,

I am in need for some help with SQL code to search multiple columns in multiple tables in an Access database. We have a database supplied to us, as part of our CAD software, which contains multiple tables each with slightly different column counts and headings.

One thing in common is that every table has the columns 'Catalog', 'Manufacturer' and 'Description'. What we need is some SQL that will search each of these columns (in every table) and return any matches for the data and the table in which it is found.

If anyone can help with the SQL code then it would be appreciated.

Thanks

 

[DCrake]

Simple Software Solutions

Inn the first instance you will need to construct a UNION query containing the tables you want to interogate. Such as:

Select Catalog, Manufacturor, Description From Table1
Union Select Catalog, Manufacturor, Description From Table2
Union Select Catalog, Manufacturor, Description From Table3

etc

You will need to add additional fields form each table that contain the data you want to look at. But ake sure you have the same number of columns from each table with matching data types.

Then create a new query based on the union query and supply the criteria in this query to filter out all the data from all the tables.

CodeMaster: