Joining 2 tables with multiple records

[dnsquires]

Hey guys,
Ok, here is the problem:

I have a query based on a couple of tables. The first table contains employee ID numbers, names, etc. The second table contains information about the employees' saving plan(s) - this can be 401K, 403B (like a 401K for not for profit companies), or a pension plan. The second table will have multiple entries for the same person (if the employee contributes to the pension plan and the 401K). What I need is a report that contains the employee information and if they contribute to a 401K, a column containing "yes" and if they do not contribute to the pension plan, a "no." I have put in the iif statement but it will return multiple records, on saying yes and one saying no because one record will be for their contributions to the pension plan and one record will be their contribution to the 401K.

How can I return just one record for each employee with a "Yes" or "No"??

Any help you could offer would be truly appreciated!

DNSquires

 

[tlbroadbent]

I'm still unclear on the requirement but this may help you get started even if it isn't the exact answer you seek. The query features a LEFT JOIN of the EmployeeTbl to a DISTINCT row sub query of the PensionTbl. The query selects one row per employee that contributed to the 401K plan. You'll need to modify table and column names as well as insert the correct criteria for the 401K contribution in the sub query.

SELECT
e.EmpID, e.EmpName,
IIf([p].[EmpID] Is Null,"No","Yes&quot AS Contributes
FROM EmployeesTbl AS e
LEFT JOIN
[SELECT Distinct EmpID
FROM PensionTbl
WHERE Contribution > 0]. AS p
ON e.EmpID = p.EmpID;
Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[BNPMike]

I'd do a sum. You want to count how many 401 records there are per employee don't you? If the total per employee is O then it's no. If it is 1 then yes.

I can't say tlbroadbent is wrong but subselects are always more tricky. mike.stephens@bnpparibas.com