I have a list of products (each row, unique) that are made using a specified material (A, B, C, D, E) which is column B (the spec matl is what the product SHOULD be made with), and the actual material that the material is being made in which is column C. I know that column B (spec matl) does not match column C (actual material).
I also have a table that lists acceptable alternate materials for each specified material:
Spec Matl Alt Matl
A A
A B
A C
C C
C E
I am having trouble using a query to find if for a particular product, the actual material for that one product matches any of the alternate materials (I have the spec matls for both tables linked). If it does then I do not want to see it in the results. The problem that I have had is that if the spec matl is A and the Actual material is B, then from the table above:
Spec Matl Alt Matl
A A
A C
would be returned which leads me to beleive that this product is being made with a wrong alternate, which is incorrect.