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.

score 1 · Answer 1 · edited Feb 02 '14 at 10:42

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')

edited Feb 02 '14 at 10:42

Gord Thompson

116,920
32
215
418

answered Feb 02 '14 at 10:33

peter.petrov

38,363
16
94
159

You seem to have the right idea, but Access won't like that query for two reasons: **(1)** Access SQL does not support an unqualified JOIN, so you'd need to use INNER JOIN. **(2)** Access will require parentheses around one of the JOINs, as illustrated by the query in the question. – Gord Thompson Feb 02 '14 at 10:39
Not sure where to put the parentheses. Can you add them in the right place please? – peter.petrov Feb 02 '14 at 10:41
NVM, found it, I think. http://stackoverflow.com/questions/7854969/sql-multiple-join-statement – peter.petrov Feb 02 '14 at 10:42
Thanks Gord but when I run this query it asks for the c.customer_id, v.customer_id and r.vehicle_id. I want to run this without any inputs – user3262510 Feb 02 '14 at 17:10

How do I do a JOIN in Access

1 Answers1