LOOKUP IN MULTIPLE TABLES

[KEL]

I have a part list with 200000 records. the prices for these items are
located in 5 different tables. I have a query that calculates the
total (qty 8 unit price).
In my calculated field how do i search all price tables to ensure I
have the price?

Thanks in advance
Kellon James

 

[Maury Markowitz]

I have a part list with 200000 records. the prices for these items are
located in 5 different tables.

Meaning what exactly? Does every record in the part list have one or
more prices stored in the other five tables? Or does each record have
a price in one of the five tables (ie, "steeringPartsPrices",
"brakePartsPrices")?

Maury

 

[John W. Vinson]

I have a part list with 200000 records. the prices for these items are
located in 5 different tables.

Ummmm... WHY!?

What distinguishes these tables?

I have a query that calculates the
total (qty 8 unit price).
In my calculated field how do i search all price tables to ensure I
have the price?

Without knowing how these tables are structured all I can suggest is that you
use a UNION query. See the help for UNION - it strings together multiple
tables into one recordset.

 

[David W. Fenton]

Without knowing how these tables are structured all I can suggest
is that you use a UNION query. See the help for UNION - it strings
together multiple tables into one recordset.

But it is best to union multiple queries, each their own WHERE
clause than it is to UNION all of the source tables and then apply a
single WHERE clause to the result. The reason is because the former
will use the indexes and the latter will not. This means writing the
SQL in code instead of having a saved UNION query.

But this kind of thing always indicates a design error, in my
opinion.