Return parts of two different records from same table in one query

Question

Although the actual schema is a bit more complex, the following should be a bit easier for me to get across.

In one table I have a list of jobs:

Job   Status  Open date   Close date
1      Closed   04/29/2009   04/30/2009
2      Open     04/30/2009  
3      Open     04/30/2009  

..and in another I have a list of notes associated with the jobs:

ID  Job   Type    Date       Text
1    1     Open     04/29/2009   Please fix my printer
2    1     Close    04/30/2009   Printer fixed
3    2     Open     04/30/2009   Please fix my monitor
4    2     Update   04/30/2009   Part required
5    3     Open     05/01/2009   Please fix my mouse

Each job will have an "Open" note, optionally one or more "Update" notes, and optionally a "Close" note. There is obviously a one-many relationship on the Job field.

What I'd like to have is a single query that returns a list of jobs, open date, opening note and, if present, closing note, like this:

Job   Status  Open date   Open note             Close note
1      Closed   04/29/2009   Please fix my printer    Printer fixed
2      Open     04/30/2009   Please fix my monitor
3      Open     04/30/2009   Please fix my mouse

My attempts at doing this always fail because I end up with the following:

Job   Status  Open date   Open note             Close note
1      Closed   04/29/2009   Please fix my printer
1      Closed   04/29/2009                            Printer fixed
2      Open     04/30/2009   Please fix my monitor
2      Open     04/30/2009   
3      Open     05/01/2009   Please fix my mouse
3      Open     05/01/2009   

..or:

Job   Status  Open date   Open note             Close note
1      Closed   04/29/2009   Please fix my printer
1      Closed   04/29/2009                            Printer fixed
2      Open     04/30/2009   Please fix my monitor
3      Open     05/01/2009   Please fix my mouse

I'm using SQL in Access 2003 and although the eventual query will be from an Excel front-end via ADO, I'm only trying to get this working from within Access at the moment.

Answer 1

Essentially you need to use the Job table as the driving table for the query, then have two joins to the Note table. The first join is an inner join for the "open" note - it is an inner join because there will always be an open note. The second join is an outer join for the "close" note, since there might not be a close note. Something like this:

select job.job, status, opendate, opennote.note, closenote.note from
  job left inner join note opennote on opennote.job = job.job
      left outer join note closenote on closenote.job = job.job

Probably the syntax in access is slightly or massively different, but that is the general idea.

Answer 2

What 1800 Information said, but that won't work correctly without a predicate to limit the join to open and closed notes, respectively:

select a.job, a.status, a.opendate, 
       b.note as opennote, c.note as closenote 
from job a
     join note b on (a.job = b.job and b.type = 'Open')
     left outer join note c on (a.job = c.job and c.type = 'Closed');

Answer 3

The solution suggested by tpdi worked when I created the command in Access, but then threw a "join expression not supported" wobbler when I tried to edit it, or run the query via ADODB from Excel.

Closing the MDB and reopening was enough to let me run it again, but as soon as I tried to view/edit it in the SQL or QBE views it would start throwing exceptions. Eventually I could not open or edit the query, even after a "compact & repair" - presumably some sort of internal corruption within Access.

Strangely though, I was able to get it working by referencing the query (SELECT * FROM noteReport;) - weird. It appears that my initial success was a fluke, as I was not able to recreate the exact query and have it work again.

However, I was eventually able to replicate the desired report with the following:

SELECT a.callReference, b.callPriority, datevalue(INT(a.openedDT)),
  b.callerFirstName & ' ' & b.callerSurname AS Caller, c.noteText, d.noteText 
FROM ((tblMain AS a 
  INNER JOIN tblCalldetails AS b ON a.callreference=b.callReference) 
  LEFT JOIN tblNotes AS c ON a.callReference=c.callReference) 
  LEFT JOIN tblNotes AS d ON a. callReference =d.callReference
WHERE a.status = 'Closed' 
  AND c.noteType='Open' 
  AND d.noteType='Closing' 
  AND INT(a.closedDT) = #5/1/2009#;

Posting this here as much as for my benefit as for anyone else that may find this via Google.