I have a table called Projects. Within this table I have many fields including:
Therefore, we can set the Status1 to "Design Plans Req" and Status1ReqDate to "03/010/2013"
I also have a related table called DesignNotes
We have so many ongoing projects now that I want to write a query that lists all the projects that require designs (Listed in the order of the Design required most urgently) and also display the last DesignNote written.
When I run the query, it works, to a point.
The problem is that when I run the query, it only shows projects that has a value for a DesignNote. But we do not always have a Design Note. Therefore, the query only returns records that have a Design Note.
How do I modify the query so it returns projects that does not have a DesignNote?
My query at the moment is:
SELECT Projects.[Project Name], Projects.ID, Projects.Status1, Projects.Status1ReqDate, First(DesignNotes.Names) AS FirstOfNames, First(DesignNotes.Note) AS FirstOfNote, First(DesignNotes.NoteDate) AS FirstOfNoteDate1
FROM Projects INNER JOIN DesignNotes ON Projects.ID = DesignNotes.ProjectID
GROUP BY Projects.[Project Name], Projects.ID, Projects.Status1, Projects.Status1ReqDate
HAVING (((Projects.Status1)="Design Plans Req"))
ORDER BY Projects.Status2ReqDate;