SQL query to check a table against a "rules table"
I'm not real familiar with Access syntax, but I think the general idea you're after would be something like this... Instead of filtering for only records that don't meet your requirements, add a calculation to your select list that indicates if the record/ruleset combination resulted in a pass or fail.  Then group by your other selected columns, and and look for groups that have no passing records and at least one failing record.