Results 1 to 4 of 4
  1. #1
    niloufar is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    Jun 2014
    Posts
    24

    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, qryEmployeeCourse
    Click image for larger version. 

Name:	access.png 
Views:	11 
Size:	293.8 KB 
ID:	17025

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    Try a compound join on EmployeeID and AssetID fields.

    Does the Username field really have EmployeeID?

    Name is a reserved word. Should avoid reserved words as field names. Also advise no spaces in naming convention. Better would be AssetTitle or Asset_Title.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    niloufar is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    Jun 2014
    Posts
    24
    Thank you June. I am not familiar with compound join can you give me an example?

  4. #4
    niloufar is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    Jun 2014
    Posts
    24
    I figured compund join and worked well. Thank you june7

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 04-10-2014, 06:36 AM
  2. Replies: 17
    Last Post: 01-26-2014, 06:53 AM
  3. Replies: 8
    Last Post: 11-04-2011, 06:52 AM
  4. Replies: 3
    Last Post: 02-02-2011, 01:00 PM
  5. Outer left join leaves out null values
    By mschles4695 in forum Queries
    Replies: 1
    Last Post: 12-22-2010, 11:43 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums