Search multiple tables

[Guest]

Hi, I have five tables which all have part numbers and prices contained
within. I want to be able to search all five tables for a specific part
number and have the corresponding price returned if found. The added
complication being that the part number and price fields are not named the
same in each table.

I did produce a query for each table using a form (criteria from a part
number entered in an unbound text control), then created reports for each. I
wanted just one report to display the information though. I tried putting
four of the reports in as sub reports which at first appeared to work. But if
the part entered does not exist in the table the main report is based on, it
does not produce any results, even though it might exist in one of the
subreport tables.

Is there an easy way round this or do I need to resort to code?

Thanks
Sue

 

[Guest]

You could use a UNION query to return a single result set which can be
queried as a single table, e.g.

SELECT PartNumber, Price
FROM Table1
UNION ALL
SELECT [Part#], PartPrice
FROM Table2
UNION ALL
SELECT [Part_#], [Part Price]
FROM Table3
UNION ALL
SELECT [Part#], [Unit Price]
FROM Table4
UNION ALL
SELECT PartNum, UnitPrice
FROM Table5;

The columns will be returned as PartNumber and Price, this being determined
by the first part of the UNION operation.

The fact that you need to do this, however, is symptomatic of a poor design
for the database. I assume that each table represents a different category
of part or something similar. This constitutes encoding data as table names,
whereas in the database relational model data should be stored as values at
column positions in rows in tables and in no other way. The correct design
would be to have one table and a column such as PartCategory. You can easily
create such a table by extending the above query to return a third column for
each category, or whatever.

SELECT PartNumber, Price, "Category1" As PartCategory
FROM Table1
UNION ALL
SELECT [Part#], PartPrice, "Category2"
FROM Table2
UNION ALL
SELECT [Part_#], [Part Price] , "Category3"
FROM Table3
UNION ALL
SELECT [Part#], [Unit Price] , "Category4"
FROM Table4
UNION ALL
SELECT PartNum, UnitPrice, "Category5"
FROM Table5;

You can use this to append the data to a new blank table with the three
columns in it. The values "Category1", "Category2" etc (or whatever values
you want to use) will be inserted as appropriate into the rows derived from
each of the original tables. You can of course have as many columns as you
wish in the UNION query, provided there is the same number of columns in each
part and all are in the same order, with the data types in each matching,
position for position.