This View shows both Heads of Household and their contact information associated with the Participant.
Here are three fields which display the Names, Emails and Mobile Phones for the Heads of Household:
,STUFF((SELECT ', '+ C.First_Name + ' ' + C.Last_Name FROM Contacts C WHERE C.Household_ID = Contact_ID_Table_Household_ID_Table.Household_ID AND C.Household_Position_ID = 1 ORDER BY C.Gender_ID DESC FOR XML PATH('')),1,2,'') AS [Heads] ,STUFF((SELECT ', '+ ISNULL(C.Email_Address,'N/A') FROM Contacts C WHERE C.Household_ID = Contact_ID_Table_Household_ID_Table.Household_ID AND C.Household_Position_ID = 1 ORDER BY C.Gender_ID DESC FOR XML PATH('')),1,2,'') AS [Head Emails] ,STUFF((SELECT ', '+ ISNULL(C.Mobile_Phone,'N/A') FROM Contacts C WHERE C.Household_ID = Contact_ID_Table_Household_ID_Table.Household_ID AND C.Household_Position_ID = 1 ORDER BY C.Gender_ID DESC FOR XML PATH('')),1,2,'') AS [Head Mobiles]
To View all minor children, change the Household Position ID to 2:
,STUFF((SELECT ', '+ C.First_Name + ' ' + C.Last_Name FROM Contacts C
WHERE C.Household_ID = Contact_ID_Table_Household_ID_Table.Household_ID
AND C.Household_Position_ID = 2 ORDER BY C.Gender_ID DESC FOR XML PATH('')),1,2,'') AS [Heads]
To View all Household Participants, remove the criteria for Household Position:
,STUFF((SELECT ', '+ C.First_Name + ' ' + C.Last_Name FROM Contacts C WHERE C.Household_ID = SELECT Contact_ID_Table_Household_ID_Table.Household_ID ORDER BY C.Gender_ID DESC FOR XML PATH('')),1,2,'') AS [HH Members]
To create the same fields on the Households Page, you can use this version. The highlighted portion is the part that has been altered for use in Households:
,STUFF((SELECT ', '+ C.First_Name + ' ' + C.Last_Name FROM Contacts C WHERE C.Household_ID = Households.Household_ID AND C.Household_Position_ID = 1 ORDER BY C.Gender_ID DESC FOR XML PATH('')),1,2,'') AS [Heads] ,STUFF((SELECT ', '+ ISNULL(C.Email_Address,'N/A') FROM Contacts C WHERE C.Household_ID = Households.Household_ID AND C.Household_Position_ID = 1 ORDER BY C.Gender_ID DESC FOR XML PATH('')),1,2,'') AS [Head Emails] ,STUFF((SELECT ', '+ ISNULL(C.Mobile_Phone,'N/A') FROM Contacts C WHERE C.Household_ID = Households.Household_ID AND C.Household_Position_ID = 1 ORDER BY C.Gender_ID DESC FOR XML PATH('')),1,2,'') AS [Head Mobiles]