Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2014
    Posts
    6

    Unanswered: Left Join Doesn't Return Unmatched Rows As Null, Access 2010

    On my Left Table I have all EMP Information joint with all possible Asset, SO all Employees are assigned to take all courses listed in the database. AND the Right Table I have Only Employees who has taken or in process of taking the course.( Both tables are queries and their results are correct)
    I would like to do Left join between two tables (queries) so I get All Employees from Left-Table joint Right-Table with new columns (Completion date & Completion status)!
    However, when I do left-join it returns all my rows and fills the blanks with the two possibilities for Completion Status which is Completed OR in Progress). The most stupid thing happens when I add Completion date and returns some random dates and fills all rows for Completion Dates and repeats those rows for following asset titles.
    My result should be a list of all EMP joint with required courses with the status of course, this person has completed the course or not and I would like to get null for all those non related rows. Will you be kind and check my code and let me know what can cause this problem? Thank you

    My code :

    Code:
    SELECT qryEmployeeCourse.[EMP ID], qryEmployeeCourse.Name, qryEmployeeCourse.Role, qryEmployeeCourse.Location, qryEmployeeCourse.Region, qryEmployeeCourse.[Asset ID], qryEmployeeCourse.[Asset Title], qryCourseStatus.[Completion Status]
    FROM qryEmployeeCourse LEFT JOIN qryCourseStatus ON qryEmployeeCourse.[EMP ID] = qryCourseStatus.Username
    GROUP BY qryEmployeeCourse.[EMP ID], qryEmployeeCourse.Name, qryEmployeeCourse.Role, qryEmployeeCourse.Location, qryEmployeeCourse.Region, qryEmployeeCourse.[Asset ID], qryEmployeeCourse.[Asset Title], qryCourseStatus.[Completion Status]
    ORDER BY qryEmployeeCourse.Name, qryEmployeeCourse.Role, qryEmployeeCours
    Attached Thumbnails Attached Thumbnails access.png  

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    random dates I doubt it
    you are joining a query to a query
    Id put money on the problem being the way you have structured the join

    if you want ALL employees then do a left join starting with the employee table, and join to other tables as required
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I agree with healdem. Tables are rock solid, queries have more subtle issues that sometimes sneak up on you when you least expect it.

    In either case, when you reference a column from the outer side of the join (which may be NULL), that reference almost always forces the JOIN to become an INNER JOIN because only the outer rows that materialize can be compared to anything.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Jun 2014
    Posts
    6

    Solved

    Thank you guys,

    I used compound join and worked well

    Thank you

Tags for this Thread

Posting Permissions

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