Comparison Query Help

[mrb783]

Hello all. I seem to be having some issues with this query and I am not quite sure why. I'm using Access 2007 and have a rather simple comparison query that tests to exclude items that exist in related tables. Here is the code:

SELECT [Rotation Descriptions].RotationDescription, [Rotation Descriptions].Supervisor
FROM [Rotation Descriptions] INNER JOIN Assignments ON [Rotation Descriptions].RotationDescription = Assignments.Position
WHERE ((([Rotation Descriptions].RotationDescription)<>[Assignments].[Position]))
GROUP BY [Rotation Descriptions].RotationDescription, [Rotation Descriptions].Supervisor;

However, this does not work. That said, if I take the "<>" out for the comparison, it works fine to include only those that are in both tables. Here is the code that works for the inclusion (read: not what I want) query:

SELECT [Rotation Descriptions].RotationDescription, [Rotation Descriptions].Supervisor
FROM [Rotation Descriptions] INNER JOIN Assignments ON [Rotation Descriptions].RotationDescription = Assignments.Position
WHERE ((([Rotation Descriptions].RotationDescription)=[Assignments].[Position]))
GROUP BY [Rotation Descriptions].RotationDescription, [Rotation Descriptions].Supervisor;

Please note that [Rotation Descriptions].RotationDescription has a 1-to-many relationship defined relating it to Assignments.Position.

Any ideas? I'm hoping this is just a simple mistake on my part.

 

[mrb783]

Okay, so I fiddled with it a little bit and decided to write my own SQL statement. Here it is:

SELECT [Rotation Descriptions].Supervisor, [Rotation Descriptions].RotationDescription
FROM [Rotation Descriptions], Assignments
WHERE ((([Rotation Descriptions].RotationDescription)<>([Assignments].[Position])))
GROUP BY [Rotation Descriptions].Supervisor, [Rotation Descriptions].RotationDescription;

This includes everything, regardless of whether or not there are matches. If i change the "<>" to "=", it works just fine to only include the ones that exist in both tables.

Still stumped.

 

[mrb783]

Okay, I figured it out. I'm not 100% sure why the other ways weren't working, but here is the solution:

SELECT [Rotation Descriptions].RotationDescription, [Rotation Descriptions].Supervisor
FROM [Rotation Descriptions] LEFT JOIN Assignments ON [Rotation Descriptions].RotationDescription = Assignments.Position
WHERE (((Assignments.Position) Is Null))
GROUP BY [Rotation Descriptions].RotationDescription, [Rotation Descriptions].Supervisor;