How do I do a JOIN in Access

Question

I have three tables and want to join them as follows: The tables are Customer, Vehicles and Reminders. The Customer is joined to Vehciles on Customer_Id, Vehicles is joined to Reminders on Vehicle_ID. I want to show the records from Customer and Vehicles which either have a record in reminders where the field MOT is <> Y or which do not have a record in Reminders. My SQL is:

SELECT Customer.Title, Customer.[First Name], Customer.Initials, Customer.[Last Name],
 Vehicles.RegNo, Vehicles.Make, Vehicles.Model, Vehicles.MOT, Reminders.MOT, Reminders.MOT_date
FROM 
(Customer 
INNER JOIN Vehicles 
ON Customer.[Customer Id] = Vehicles.[Customer Id]) 
INNER JOIN Reminders 
ON Vehicles.[Vehicle ID] = Reminders.[Vehicle_ID]
WHERE Reminders.MOT <>"Y";

This displays no results but I have one record which is in all three with a value blank in Reminders.MOT and a number of records which are in Customer and Vehicles with no record in Reminders.

Answer 1

Try this query. I assume r.MOT is not nullable in Reminders.
So the r.MOT is null below is an indication that a Reminders
record does not exist.

    select distinct 
    c.Title, c.[First Name], c.Initials, c.[Last Name], 
    v.RegNo, v.Make, v.Model, v.MOT, r.MOT, r.MOT_date
    from
    (Customer c
    inner join Vehicles v on c.Customer_Id = v.Customer_Id)
    left join Reminders r on v.Vehicle_ID = r.Vehicle_ID
    where
    (r.MOT is null)
    or
    (r.MOT <> 'Y')