Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2009
    Posts
    38

    Unanswered: Query not returning required results

    Hi,

    I have a table called Projects. Within this table I have many fields including:

    Status1
    Status1ReqDate

    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;


    Thanks

    Nigel

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Change INNER to LEFT.
    Paul

  3. #3
    Join Date
    Jul 2009
    Posts
    38
    Paul,

    Many thanks, working now.

    I don't expect you to write a book, but could you explain briefly why that fixed it so I can learn from it.

    Cheers

    Nigel

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    With the query in design view, double click on a join line to edit the join. The 3 options you see will set the SQL to INNER, LEFT or RIGHT as appropriate.
    Paul

  5. #5
    Join Date
    Jul 2009
    Posts
    38
    Thanks Paul.

    Simple now I know!

    Nigel

  6. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Happy to help Nigel!
    Paul

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •