Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old April 12th, 2005, 09:13 AM
Registered User
 
Join Date: Aug 2003
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default Why doesn't this LEFT JOIN work?

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;


 
Old April 12th, 2005, 10:14 AM
Friend of Wrox
 
Join Date: Sep 2003
Posts: 155
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to leehambly
Default

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;
 
Old April 13th, 2005, 04:49 AM
Registered User
 
Join Date: Aug 2003
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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






Similar Threads
Thread Thread Starter Forum Replies Last Post
left outer join keyvanjan Classic ASP Professional 0 February 5th, 2006 11:54 AM
left join msrnivas Classic ASP Databases 2 October 15th, 2004 07:37 AM
Oracle 8i inner join and left join problem puteri_84 Oracle 2 August 19th, 2004 07:14 AM
LEFT JOIN syntax 12th_Man Oracle 2 October 27th, 2003 09:23 PM
Left Join error mariakovacs Access ASP 2 October 13th, 2003 04:16 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.