Advise on Query Joins

[kentwoodjean]

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);

 

[lynchg]

Your query looks like it should retrieve what you are looking for based on your description.

This query would return just one record only if there was just one record in tblMembership. I suspect that the problem lies somewhere else.

Run this query on it's own and see what results you get.

 

[kentwoodjean]

Perhaps I need to back up a minute and you can advise me. I have so many test queries/reports that I want to go back to square one. As an end result these are the fields I want on my report and the tables they come from:

tblMembership

Contract_Num
Group#
Proper Name
Steet
City
State
Zip
Birthday
MemRelCde
Gender
Age

tblComplianceLog

ContractNum** (double stars can join to member table)
Rel Code**
CatID
Compliant
Comments

Category (8 are listed)

CatCode * (single star can join with CatID)
CatName
Criteria
TimeFrame

I want all fields to appear on my report except CatCode(CatID) and Rel Code (MemRelCde). I also want all 8 categories to appear even though there are no details entered at this point for a specific customers. Bottom line is all members must get this report and all categories must be shown though some may be blank in the Compliant and Comments field.

I have tried every way and then some to make this work out and cannot seem to do it. Even tried a sub report but to no avail. Any ideas?