Search multiple tables

[hughess7]

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

 

[Ken Sheridan]

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.

 

[hughess7]

Thanks for your reply Ken, I will try a union query.

I know its bad design, its not my database and I have already told the user
I would have done it the way you suggested. The tables are all imported
information from different sources which I think he imports on a regular
basis. I've just been asked if I can produce this one price report for him.

Thanks again for your help
Sue

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.

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

 

[hughess7]

Hi again Ken, I've copied your union query example. The only problem is I
need to be able to identify which country the Part Number and Price relates
to, each table holds one countries data eg Germany Price File, UK Price File
etc

Is this possible with this type of query?

Thanks
Sue

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.

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

 

[Ken Sheridan]

You can do that very easily; just include the name of the country as a
constant for an extra column in the UNION operation's result set, e.g.

SELECT PartNumber, Price, "UK" As Country
FROM [UK Price File]
UNION ALL
SELECT [Part#], PartPrice, "Germany"
FROM [Germany Price File]
UNION ALL
SELECT [Part_#], [Part Price], "France"
FROM [France Price File]
UNION ALL
SELECT [Part_#], [Part Price], "Spain"
FROM [Spain Price File]
UNION ALL
SELECT PartNum, UnitPrice, "Italy"
FROM [Italy Price File];

The result set will have a column Country with the name of the country from
whose table the row was derived.

Incidentally, you might have noticed that I used UNION ALL rather that a
simple UNION operations. A simple UNION operation removes any duplicate rows
from the result set, but it does slow down performance, so if there will not
be duplicates, or there might be duplicates but you want to keep them, always
use a UNION ALL operation to improve performance. In this case there should
not be any duplicates as, even if two of the tables had the same part number
and price values in them, the inclusion of the Country column means that
these will not be duplicate rows in the result set.

 

[hughess7]

Thanks Ken! Not used a union query before... nice to learn new things thanks.
One slight problem, since adding the country to the query as per your
explanation when I run it I get a parameter Value box for query1.Part Number
appear and not sure why? If I just press enter it appears to display the
results correctly still but can I stop it from asking for a parameter? I've
checked the tables and it looks like all the field names are correct in the
query so not sure what is causing this.

Sue

You can do that very easily; just include the name of the country as a
constant for an extra column in the UNION operation's result set, e.g.

SELECT PartNumber, Price, "UK" As Country
FROM [UK Price File]
UNION ALL
SELECT [Part#], PartPrice, "Germany"
FROM [Germany Price File]
UNION ALL
SELECT [Part_#], [Part Price], "France"
FROM [France Price File]
UNION ALL
SELECT [Part_#], [Part Price], "Spain"
FROM [Spain Price File]
UNION ALL
SELECT PartNum, UnitPrice, "Italy"
FROM [Italy Price File];

The result set will have a column Country with the name of the country from
whose table the row was derived.

Incidentally, you might have noticed that I used UNION ALL rather that a
simple UNION operations. A simple UNION operation removes any duplicate rows
from the result set, but it does slow down performance, so if there will not
be duplicates, or there might be duplicates but you want to keep them, always
use a UNION ALL operation to improve performance. In this case there should
not be any duplicates as, even if two of the tables had the same part number
and price values in them, the inclusion of the Country column means that
these will not be duplicate rows in the result set.

Hi again Ken, I've copied your union query example. The only problem is I
need to be able to identify which country the Part Number and Price relates
to, each table holds one countries data eg Germany Price File, UK Price File
etc

Is this possible with this type of query?

Thanks
Sue

 

[hughess7]

Found it! I noticed in the query properties that the sort order was set to
Query1.Part Number, took out the query1 and all working.

Thanks again for your help .

Sue

Thanks Ken! Not used a union query before... nice to learn new things thanks.
One slight problem, since adding the country to the query as per your
explanation when I run it I get a parameter Value box for query1.Part Number
appear and not sure why? If I just press enter it appears to display the
results correctly still but can I stop it from asking for a parameter? I've
checked the tables and it looks like all the field names are correct in the
query so not sure what is causing this.

Sue

You can do that very easily; just include the name of the country as a
constant for an extra column in the UNION operation's result set, e.g.

SELECT PartNumber, Price, "UK" As Country
FROM [UK Price File]
UNION ALL
SELECT [Part#], PartPrice, "Germany"
FROM [Germany Price File]
UNION ALL
SELECT [Part_#], [Part Price], "France"
FROM [France Price File]
UNION ALL
SELECT [Part_#], [Part Price], "Spain"
FROM [Spain Price File]
UNION ALL
SELECT PartNum, UnitPrice, "Italy"
FROM [Italy Price File];

The result set will have a column Country with the name of the country from
whose table the row was derived.

Incidentally, you might have noticed that I used UNION ALL rather that a
simple UNION operations. A simple UNION operation removes any duplicate rows
from the result set, but it does slow down performance, so if there will not
be duplicates, or there might be duplicates but you want to keep them, always
use a UNION ALL operation to improve performance. In this case there should
not be any duplicates as, even if two of the tables had the same part number
and price values in them, the inclusion of the Country column means that
these will not be duplicate rows in the result set.

Hi again Ken, I've copied your union query example. The only problem is I
need to be able to identify which country the Part Number and Price relates
to, each table holds one countries data eg Germany Price File, UK Price File
etc

Is this possible with this type of query?

Thanks
Sue