CODEWITHSUNDEEP
sqlms-accessms-access-2007
Chuck
Chuck

Reputation: 1226

Checking multiple columns from a table against a column in another table

Table A Name, Address, ..., Item1, Item2, ..., Item10

Table B ProductID

I want to run an SQL statement (in access) to Select all records where any item ([Item1] - [Item10]) that does not match a Product code found in [ProductID] in Table B. I am trying to catch all errors in the file before I export it into our inventory software and it can not find the item and we have to start over.

I have been trying to make a LEFT JOIN work but with no success.

Upvotes: 1

Views: 952

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123849

For [TableA]...

ID  Name   Item1     Item2     Item3   
--  -----  --------  --------  --------
 1  Name1  Product1  Product2  Product3
 2  Name2  Product2                    
 3  Name3  Product1  Product4          
 4  Name4  Product1  Produtc2  Product3

...and [TableB]...

ProductID
---------
Product1 
Product2 
Product3

...the query...

SELECT * FROM TableA
WHERE IIf(IsNull(Item1), False, DCount("*", "TableB", "ProductID='" & Item1 & "'") = 0)
    Or IIf(IsNull(Item2), False, DCount("*", "TableB", "ProductID='" & Item2 & "'") = 0)
    Or IIf(IsNull(Item3), False, DCount("*", "TableB", "ProductID='" & Item3 & "'") = 0)

...produces:

ID  Name   Item1     Item2     Item3   
--  -----  --------  --------  --------
 3  Name3  Product1  Product4          
 4  Name4  Product1  Produtc2  Product3

Upvotes: 1

Related Questions