kentwoodjean
Technical User
Have a query built from a table and a query. I use this for a letter that is sent to customers in a membership file but the join is not correct and instead a creating a letter for each member, I can only get one member name to show up. All data types align and have tried different ways of doing this. What would be the correct way to join these files when I want all members from the tblMembership and only those from the qryCompliance where the contract# and memRelCode match?
SELECT tblMembership.CONTRACT_NUM, tblMembership.[First Name], tblMembership.[Last Name], tblMembership.Street, tblMembership.City, tblMembership.State, tblMembership.Zip, tblMembership.[Phone#], tblMembership.MemRelCde, tblMembership.GenderCd, tblMembership.MemBirth, tblMembership.MemAge, tblMembership.EffectiveDate, qryCompliance.RecvdDt, qryCompliance.CatID, qryCompliance.CatName, qryCompliance.Criteria, qryCompliance.TimeFrame, qryCompliance.Compliant, qryCompliance.Comments, qryCompliance.Mail, qryCompliance.Phone, qryCompliance.ComplianceActvity, qryCompliance.ComplianceDt, tblMembership.[Group#]
FROM tblMembership LEFT JOIN qryCompliance ON (tblMembership.MemRelCde = qryCompliance.RelCode) AND (tblMembership.CONTRACT_NUM = qryCompliance.ContractNum);
SELECT tblMembership.CONTRACT_NUM, tblMembership.[First Name], tblMembership.[Last Name], tblMembership.Street, tblMembership.City, tblMembership.State, tblMembership.Zip, tblMembership.[Phone#], tblMembership.MemRelCde, tblMembership.GenderCd, tblMembership.MemBirth, tblMembership.MemAge, tblMembership.EffectiveDate, qryCompliance.RecvdDt, qryCompliance.CatID, qryCompliance.CatName, qryCompliance.Criteria, qryCompliance.TimeFrame, qryCompliance.Compliant, qryCompliance.Comments, qryCompliance.Mail, qryCompliance.Phone, qryCompliance.ComplianceActvity, qryCompliance.ComplianceDt, tblMembership.[Group#]
FROM tblMembership LEFT JOIN qryCompliance ON (tblMembership.MemRelCde = qryCompliance.RelCode) AND (tblMembership.CONTRACT_NUM = qryCompliance.ContractNum);