Complex Query Question with Joins

[amacfarl]

Folks,

Have two tables:

TblHier Hierarchy Table
Fields Level0, Level1, Level2, Level3, Level4 (all int)

TblProduct Product Listings
Fields ProductNumber, Level0, Level1, Level2, Level3, Level4 (all int)

I am using the following query to count how many products have the correct hierarchy as detailed in the Hierarchy Table.

SELECT
Count(*)
FROM
TblProduct, TblHier
WHERE
TblProduct.Level0 = TblHier .Level0 AND
TblProduct.Level1 = TblHier .Level1 AND
TblProduct.Level2 = TblHier .Level2 AND
TblProduct.Level3 = TblHier .Level3 AND
TblProduct.Level4 = TblHier .Level4

My question is how to count and list the products that do not following the correct hierarchy... obviously this query does not work...

SELECT
Count(*)
FROM
TblProduct, TblHier
WHERE
TblProduct.Level0 <> TblHier .Level0 AND
TblProduct.Level1 <> TblHier .Level1 AND
TblProduct.Level2 <> TblHier .Level2 AND
TblProduct.Level3 <> TblHier .Level3 AND
TblProduct.Level4 <> TblHier .Level4


THANKS IN ADVANCE FOR YOUR HELP

 

[Golom]

Since &quot;Bad&quot; is the converse of &quot;Good&quot; ...

SELECT
Count(*)
FROM
TblProduct, TblHier
WHERE NOT (
TblProduct.Level0 = TblHier .Level0 AND
TblProduct.Level1 = TblHier .Level1 AND
TblProduct.Level2 = TblHier .Level2 AND
TblProduct.Level3 = TblHier .Level3 AND
TblProduct.Level4 = TblHier .Level4 )