Problem proofind data to M-to-M relationship

[heecack]

I have a many-to-many relationship set up in 3 tables:

tbl_ADPMaster
-EmployeeID, FileNum, Name
tbl_Crews
-CrewID, CrewNum
tbl_Employee_Crew
-ID, EmployeeID, CrewID

A Filenum (Employee) can belong to multiple crewnum. A CrewNum can have
multiple Filenum (Employees).

The two keys from these tables are in the third table as foreign keys. I
believe I have all of this setup correctly. It works for all other things I
need in the database.

THE PROBLEM:

I have a 4th table that contains payroll data that I need to proof against t
the above tables on a weekly basis.

tbl_Payroll HSK
- FileNum, Crew, Work Date

For every Crew in the table show me any Filenums that do not match.
For every Filenum in the table, show me any Crews that do not match.

The many to many relationship is throwing me off. Everything I try does not
seem to work.

Any help would be appreciated.

Thanks,
Sheila

 

[vanderghast]

Assuming by "not match" you mean a value in PayrolHSK not in
Employee_Crew:


SELECT a.crewID, a.employeeID,
b.crewID IS NULL as crewNotMatching,
b.employeeID IS NULL as employeeNotMatching
FROM payrolHSK AS a LEFT JOIN employee_crew AS b
ON a.crewID = b.crewID
AND a.employeeID = b.employeeID
WHERE b.crewUD IS NULL OR b.employeeID IS NULL



Vanderghast, Access MVP