Join Query Based on Group By

[JohnHarkins]

Object: Generate two forms of last name depending on whether or not there are one or more family members with the same last name at the same address. I want to send mail specifically to a person using his/her first and last names at a given address when there's only one record for that address; otherwise, the mail should be addressed to the "x" household.

I can fairly easily create a name line in an address label when there are multiple people with the same last name at a given address; e.g., The Smith Family when there are two records, one each for John and Betsie. I used a "group by" on each field participating with a count on a unique record ID (“RecId”) to isolate those cases where I "count" the members of a family; the SQL statement follows:

<Query #1>
SELECT DISTINCTROW tblBase.Lname, tblBase.[House#], tblBase.[House#_sfx], tblBase.Stname, tblBase.[Apt#], Count(tblBase.RecId) AS CountOfRecId, &quot;The &quot; & [lname] & &quot; Family&quot; AS NameLine
FROM tblBase
GROUP BY tblBase.Lname, tblBase.[House#], tblBase.[House#_sfx], tblBase.Stname, tblBase.[Apt#]
HAVING (((Count(tblBase.RecId))>1));

The difficulty is handling the case when there is only a record for John at an address. I thought that I would isolate those families with only one member similar to the above; the SQL statement follows:

<Query #2>
SELECT DISTINCTROW tblBase.Lname, tblBase.[House#], tblBase.[House#_sfx], tblBase.Stname, tblBase.[Apt#], Count(tblBase.RecId) AS CountOfRecId
FROM tblBase
GROUP BY tblBase.Lname, tblBase.[House#], tblBase.[House#_sfx], tblBase.Stname, tblBase.[Apt#]
HAVING (((Count(tblBase.RecId))=1));

This works by itself to isolate those with a last name and only one resident at an address; however, when I want to want to join the above query's result with the &quot;person&quot; database (tableBase) in order to create a name line, such as &quot;John Smith,&quot; the join only works if each address component (House#, House#_sfx, Stname, and Apt# have non-null and non-blank entries in each table. Since most addresses have only a house number and street name, this excludes the majority of records in the result. Here's the SQL that includes Query #2 to join the result of the query and the person database (tblBase):

<Query #3>
SELECT qrySingleHouseholdMember.Lname, qrySingleHouseholdMember.[House#], qrySingleHouseholdMember.[House#_sfx], qrySingleHouseholdMember.Stname, qrySingleHouseholdMember.[Apt#], qrySingleHouseholdMember.CountOfRecId
FROM qrySingleHouseholdMember INNER JOIN tblBase ON (qrySingleHouseholdMember.Lname = tblBase.Lname) AND (qrySingleHouseholdMember.[House#] = tblBase.[House#]) AND (qrySingleHouseholdMember.Stname = tblBase.Stname) AND (qrySingleHouseholdMember.[Apt#] = tblBase.[Apt#]) AND (qrySingleHouseholdMember.[House#_sfx] = tblBase.[House#_sfx]);

I suspect the problem is that the result of Query #2 (the group-by query) creates blanks/nulls which don't compare properly with the nulls/blanks in tblBase when joined as in Query #3 and when one of the elements of the address (e.g., Apt#) is not present. How can I do the join with Query #2 properly? I thought of doing a &quot;group by&quot; on tblBase so that I can create nulls/blanks the same way they were in Query #2, but that seems like excess effort. In order to diagnose these sorts of situations, is there a way to detect when a field in a record (or query result) contains a null and when it contains an empty entry?

Is there a better way to accomplish what I want to do, as stated in the &quot;Object&quot; above? Sorry for the long query.

Thanks,

John John Harkins

 

[RickSpr]

You're right that nulls are causing your problem. Null is never considered equal to anything, even another Null, so a join that involves comparing nulls will always omit the record.

I would suggest adding First(RecId) as a field in Query#2, then modifying your join to match on the RecId field alone. Rick Sprague