- NameCounter - a numeric value that is unique for every person in the table
- FamNu - a numeric value shared by all members of the same family; it is helpful to realize that this value is the same as the head of household's NameCounter value.
- UnitNu - a numeric value indicating the family position in the household; the head of house value is zero (0) and the spouse value is one (1).
HeadOfHouse = HH.FirstMiddle + ' ' + HH.LastName,
Spouse = SP.FirstMiddle + ' ' + SP.LastName
Shelby.NANames as HH left join
Shelby.NANames as SP on HH.FamNu = SP.FamNu and SP.UnitNu = 1
HH.UnitNu = 0 and HH.TitleCounter > -1
HeadOfHouse = ChosenNames.FirstMiddle + ' ' + ChosenNames.LastName,
Spouse = Spouses.FirstMiddle + ' ' + Spouses.LastName
Shelby.NANames as ChosenNames left join
Shelby.NANames as Spouses on ChosenNames.FamNu = Spouses.FamNu and Spouses.UnitNu = ABS(ChosenNames.UnitNu - 1)
any conditional statements