Query that includes records with no match

[Realtoast]

Hello Experts,



I have several tables I need to combine to create a single table. Easy enough. But here is my challenge.

One table has 360 records of all employees. Another table has 180 records with employees who have benefits (Medical, Dental, Vision, etc.). Another table has 129 of the employees who participate in the 401k plan, etc. When I run a query, with all three tables (EmployeeID as the index in each table), of course the resulting table has just 116 records, because not all employees have the 401k and not all those with a 401k have other benefits.

What I would like is to have the query result in all 360 employee records, with null values where an employee is not participating in either 401k or other benefits.

Is this even possible? If so, what would be the method? Perhaps a multi-step process?

Thanks!

[ssanfu]

Yes, it is possible. I think you need to use left joins.

One table has 360 records of all employees

Lets call this table "tblEmployees" with a PK field "EmployeeID_PK"

Another table has 180 records with employees who have benefits

Lets call this table "tblBenefits" with a FK field "EmployeeID_FK".

Another table has 129 of the employees who participate in the 401k plan

Lets call this table "tbl401k" with a FK field "EmployeeID_FK".

The query would look something like

SELECT tblEmployees.FName, tblBenefits.BenefitName, tbl401k.Amount
FROM (tblEmployees LEFT JOIN tblBenefits ON tblEmployees.EmployeeID_PK = tblBenefits.EmployeeID_FK) LEFT JOIN tbl401k ON tblEmployees.EmployeeID_PK = tbl401k.EmployeeID_FK;

Once you add the tables to the query grid, double click on the joining line and select #2: "Include all records from tblEmployees and only those records from tblBenefits where the joined fields are equal.

Do the same for the 401k table.

[Realtoast]

Thanks, Steve. I'm on it right now. Appreciate the quick response. I'm new to this, but I'll report back if I'm successful.

[Realtoast]

Outstanding! Works like a charm. Thanks, Steve!

[ssanfu]

You are welcome....