I am trying to write a query that will show a list of appliances with their installation date and the date of any service that has taken place after the appliance was installed.

As they won't all have a service date, I want to show this using the left join, but when I tried the statement below it works just like an INNER JOIN as far as I can see.

The report should show something like this:

Manuf Date Purch CustomerID Date Serv JobTypeID JobStatus
VOKERA 30/07/2004 619 07/04/2005 2 0
VOKERA 04/05/2004 754
Vokera 23/03/2005 1843
BAXI 05/07/2004 1950 19/11/2004 2 0
VOKERA 24/05/2004 2343 25/11/2004 2 0
VOKERA 28/05/2004 2523 11/11/2004 2 0
VOKERA 20/10/2004 2768 22/03/2005 2 0

instead it only shows records that have a Service date.

My query at the moment looks like this:

SELECT A.Manufacturer, A.DateInstalled, A.CustomerID, w.DateFinished, w.JobTypeID, w.JobStatus
FROM Appliances AS A LEFT JOIN Workorders AS w ON A.CustomerID = w.CustomerID
WHERE (((A.DateInstalled)>#3/31/2004#) AND ((w.DateFinished)>[a].[dateinstalled]) AND ((w.JobTypeID)=2) AND ((w.JobStatus)="0"))
ORDER BY A.CustomerID;

Youve got the left join done fine, but have then basically filtered out all the null records by putting criteria on the workorders table...

w.jobtypeid = 2
w.jobstatus = "0"
w.datefinished > a.dateinstalled

For any Appliances record which has no WorkOrder record these fields will be null, so you need to include nulls as well as your criteria...

SELECT A.manufacturer, A.dateinstalled, A.customerid, w.datefinished, w.jobtypeid, w.jobstatus
FROM Appliances AS A LEFT JOIN Workorders AS w ON A.customerid = w.customerid
WHERE (((A.dateinstalled)>"#31/03/2004#") AND ((w.datefinished)>[a].[dateinstalled] Or (w.datefinished) Is Null) AND ((w.jobtypeid)=2 Or (w.jobtypeid) Is Null) AND ((w.jobstatus)="0" Or (w.jobstatus) Is Null))
ORDER BY A.customerid;

Ah, now the penny drops. Thanks for that Lee.