Q:

Show all records from one table and if they have a record in another table

I am currently looking to do something which I assume to be super simple but am currently missing for some reason or another.  I have 2 tables one is a list of all company drivers and another is a list of the most recent incidents for the drivers.  I am looking to make a query to take a list of drivers and show if they have an incident or not. The query I have now will only show the drivers that have an incident and not the ones that do not.  I am assuming it has something to do with my joins.
SELECT DriverProfile.DriverID, DriverProfile.FirstName, DriverProfile.LastName, dbo_manpowerprofile.mpp_senioritydate AS [Seniority Date], Last(Incidents.[Event Date]) AS [Incident Date]
FROM (DriverProfile LEFT JOIN dbo_manpowerprofile ON DriverProfile.DriverID = dbo_manpowerprofile.mpp_id) LEFT JOIN Incidents ON DriverProfile.DriverID = Incidents.Driver
WHERE (((Incidents.Type)<>"OBS") AND ((Incidents.Preventability)<>"TNP" And (Incidents.Preventability)<>"NTNP") AND ((DriverProfile.ActiveYN)="Y"))
GROUP BY DriverProfile.DriverID, DriverProfile.FirstName, DriverProfile.LastName, dbo_manpowerprofile.mpp_senioritydate;

A:

The problem is actually in your WHERE clause. Applying criteria to the left joined tables (dbo_manpowerprofile and Incidents), effectively makes those joins, inner joins. If you are working in Access, you need to create separate queries for those tables and apply the criteria there. Then left join those queries to DriverProfile. If you were working in SQL Server, you could include the criteria in the join itself.

