I've created an append query that uses a join that includes all records in the essential accountability table and only those in the performance criteria table where the joined fields (which is essential_id) are equal. My problem is that when I have a record from essential accountability table that does not have a match in the performance criteria table, the essential id field in being left blank in my append query. Here is the SQL -

INSERT INTO tblTemp_Role_Desc ( Dept_No, Job_Code, Job_Title, Department, Essential_Id, Performance_Id, Reassigned, Essential_Accountability, Performance_Criteria )
SELECT Tbl_Role_Desc_Info.Dept_No, Tbl_Essential_Accountability.Job_Code, Tbl_Job_Titles.Job_Title, Tbl_Departments.Department, Tbl_Performance_Criteria.Essential_Id, Tbl_Performance_Criteria.Performance_Id, Tbl_Essential_Accountability.Reassigned, Tbl_Essential_Accountability.Essential_Accountabil ity, Tbl_Performance_Criteria.Performance_Criteria
FROM (Tbl_Departments INNER JOIN Tbl_Role_Desc_Info ON Tbl_Departments.Dept_No = Tbl_Role_Desc_Info.Dept_No) INNER JOIN ((Tbl_Essential_Accountability INNER JOIN Tbl_Job_Titles ON Tbl_Essential_Accountability.Job_Code = Tbl_Job_Titles.Job_Code) LEFT JOIN Tbl_Performance_Criteria ON Tbl_Essential_Accountability.Essential_ID = Tbl_Performance_Criteria.Essential_Id) ON (Tbl_Job_Titles.Job_Code = Tbl_Role_Desc_Info.Job_Code) AND (Tbl_Role_Desc_Info.Job_Code = Tbl_Essential_Accountability.Job_Code)
WHERE (((Tbl_Essential_Accountability.Job_Code)=[Forms]![Frm_Select_Job_Title]![Job_Title_Combo]))
ORDER BY Tbl_Essential_Accountability.Job_Code, Tbl_Performance_Criteria.Essential_Id, Tbl_Performance_Criteria.Performance_Id;

What am I doing wrong?

Thanks - Terrie