Lookup Values From Table

[Joe Williams]

I have a query that has part numbers as one of the fields. For each Part
number, I would like to lookup a certain operation in a table that has the
various operations performed on that part number. If it finds that
operation, it returns YES, if it doesn't it returns NO.

For instance, in the main query I have part number XYZ. In the operations
table, part number XYZ might have one record for WELDING, one for STAMPING,
and one for GRINDING.

I want the main query to look into the operations query, look at all the
records, and if it sees a record with the operation WELDING then it returns
a YES.

How can I accomplish this while keeping only one record for the part number
in the main query?

Thanks

Joe

 

[Guest]

Create a query "qryWelding"

Select [Part Number] from tblOperations where OPType = 'Welding';

Then use it in your query

Select MainTable.*, iif(isnull(qryWelding.[Part Number]),'No','Yes') as
Welding from MainTable
left join qryWelding on MainTable.[Part Number] = qryWelding.[Part Number]