Infuriatingly simple name and address list

Topic

New Reply

In these days, this must come up, but I keep having some thing or the other wrong.

I have tables for household and people; people are marked as children or adults. I want to report hh info (address, phone, etc), and a list of names in the hh.

But, suppose Jane Dough is married to Richard Roe, and they have two children, jessica Roe and Aaron Roe (they are proper yuppies). Under the D’s, I want to see Jane Dough and the list of people, and the same under Richard Roe in the Rs.

On the other hand, Bill and Mary Conventional also have two children, and they should have only one listing under the Cs.

This would be easy in a procedural language…

Reply | Quote

Replies

I think you have several ways of doing this. The way you choose will depend on how you decide to store the data and what questions you want to answer.

One solution might be this:
From your post, I assume you have one table for the address details possibly named

Reply | Quote

Alternately, you could add an additional field for “Alternate Name” to capture the other family name that the record should be cross-referenced against. Your query could then check to see if surname you are checking for matches either the primary or alternate name. If there is no alternate name needed for the record, the field can be left as Null.

Sample Data:

LastName	AltName	Matches If
Roe	Doe	Either
Doe	Roe	Either
Conventional	Null	Conventional

Reply | Quote

Thanks to both of you; will think about it and do something. I appreciate your help!

Reply | Quote

Got it!
Create a query which contains only people table’s household key, lastname, and a calculated field, (LastName & HHKey) (SELECT DISTINCT).
Use this as the base of a second query, which contains the fields you want.
Group the report on (LastName & HHKey) and HHKey, and sort details on People Last and First Names. In the Last-Name HH heading, display the last name from the 1st query. Header for HHid shows household information; details shows people.

Reply | Quote