Using a Query to Get Heads of Households

[dbqph]

Hello,

I'm trying to build a query that will get me the Heads of Households and
here is what I have to work with:

tblHouseholds:
HouseholdID (PrimaryKey)
PrimaryMemberID (can be related to tblMembers.MemberID)

tblMembers:
MemberID (PrimaryKey)
HouseholdID
Child (Boolean)

I could populate my tblHouseholds with a SecondaryMemberID if needed but
would prefer to have a query that displays:

HouseholdID, PrimaryFirst, PrimaryLast, SecondaryFirst, SecondaryLast

(keeping in mind that some households only have 1 adult head of household)

Suggestions?

Thanks,

dbqph

 

[John W. Vinson]

Hello,

I'm trying to build a query that will get me the Heads of Households and
here is what I have to work with:

tblHouseholds:
HouseholdID (PrimaryKey)
PrimaryMemberID (can be related to tblMembers.MemberID)

tblMembers:
MemberID (PrimaryKey)
HouseholdID
Child (Boolean)

I could populate my tblHouseholds with a SecondaryMemberID if needed but
would prefer to have a query that displays:

HouseholdID, PrimaryFirst, PrimaryLast, SecondaryFirst, SecondaryLast

(keeping in mind that some households only have 1 adult head of household)

Suggestions?

Thanks,

dbqph

Not sure I understand! How do you identify the "secondary" family member? What
if there are five adults in the household (grandpa, grandma, mom and dad, and
Uncle Jim)? Where are the first and last names - in Members?

You'll *probably* need to join tblMembers to tblHouseholds *twice*: try

SELECT tblHouseholds.HouseholdID, MemP.FirstName AS PrimaryFirst,
MemP.LastName AS PrimaryLast, MemS.FirstName AS SecondaryFirst, MemS.LastName
AS SecondaryLast
FROM (tblHouseholds INNER JOIN tblMembers AS MemP
ON tblHouseholds.HouseholdID = MemP.HouseholdID
AND tblHouseholds.PrimaryMemberID = MemP.MemberID)
LEFT JOIN tblMembers AS MemS
ON tblHouseholds.HouseholdID = MemS.HouseholdID
WHERE MemS.Child = False OR MemS.Child IS NULL
AND MemS.MemberID <> tblHouseholds.HouseholdID
OR MemS.MemberID IS NULL;


John W. Vinson [MVP]

 

[dbqph]

John,

Thank you for the initial stab at this. I had tried this approach (and
re-tried your SQL) and wound up with more records than I have households as
the recordset contained two records for each household that had two
"non-childs" similar to the following:

HouseholdID, PrimaryFirst, PrimaryLast, SecondaryFirst, SecondaryLast
1, Jack, Anderson, Jack, Anderson
1, Jack, Anderson, Jill, Anderson
2, Bob, Roberts, Bob, Roberts
3, Jack, Daniels, Jack, Daniels
3, Jack, Daniels, Jane, Comfort
4, John, Doe, John, Doe
etc.

I am trying to create a membership directory of the households and you can
safely assume that a household will have at most 2 adult members. I had
successfully created the results I was after in another database through a
large series of queries and union queries but that was a lot of work that I'd
prefer to not have to duplicate (plus it is very slow).

My goal is to get one record per household with:
HouseholdID, PrimaryFirst, PrimaryLast, SecondaryFirst, SecondaryLast
1, Jack, Anderson, Jill, Anderson
2, Bob, Roberts, Null, Null
3, Jack, Daniels, Jane, Comfort
4, John, Doe, Null, Null
etc.

Hopefully this sheds some light on the solution! Thanks again for your
insight!

dbqph

 

[John Spencer]

Sounds as if you would need three queries to solve this problem. If you
want additional fields then you need to add them in.

QPrimary
SELECT M.HouseHoldID, M.MemberID
FROM TblMembers as M INNER JOIN TblHouseHolds as H
ON M.HouseHoldID = H.HouseholdID AND
M.MemberID = H.PrimaryMemberID

QSecond
SELECT M.HouseHoldID, M.MemberID
FROM TblMembers as M INNER JOIN TblHouseHolds as H
ON M.HouseHoldID = H.HouseholdID
WHERE M.MemberID <> H.PrimaryMemberID and Child = False

Now using those two queries as the source for a third
SELECT P.HouseHoldID, P.MemberID, S.MemberID
FROM QPrimary as P LEFT JOIN QSecond as S
ON P.HouseHoldId = S.HouseHoldID

You might be able to do that all in one query.
This works only if you don't require brackets around table and field names
or parameters in the subqueries

SELECT P.HouseHoldID, P.MemberID, S.MemberID
FROM (
SELECT M.HouseHoldID, M.MemberID
FROM TblMembers as M INNER JOIN TblHouseHolds as H
ON M.HouseHoldID = H.HouseholdID AND
M.MemberID = H.PrimaryMemberID) as P
LEFT JOIN (
SELECT M.HouseHoldID, M.MemberID
FROM TblMembers as M INNER JOIN TblHouseHolds as H
ON M.HouseHoldID = H.HouseholdID
WHERE M.MemberID <> H.PrimaryMemberID and Child = False) as S
ON P.HouseHoldId = S.HouseHoldID

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[dbqph]

John,

I think that will get it. It was the "FROM (SELECT M.HouseHoldID..." syntax
I was unsure of. Thank you for your assistance and insight.

dbqph