Directory

[musicianman4god]

I have a DB table with records for each person that is part of a church. I am trying to create a query that will combine Family members into one address as follows:

{Head of Household FirstName} {[if married] & 'and' & {spouse firstname}
street address
{City}, {State} {Zip}
{Primary Phone}
{Primary Email}
{[if any children] Child1 Child2, ... & 'and' & last child

I am able to create most of this except of the last line.
There are collums for HouseholdID, Household Member Type (Head of House, Spouse, Child, Other) First Name, Last name, Street Address, City, State, Zip, eMail address, Phone, and Marital Status.

Any suggestions (VB, SQL, etc...) Would be great.

 

[Mr. B]

Try creating a query that will produce a list from tyour Household table where the Household menber type is equal to "Child" or "Other" (if you want the "other" records included) and include the HouseholdID field in your query. Then use this query in your existing query that is returning the other information and link this query to your table using the HouseholdID field.

 

[musicianman4god]

ok, think this would work if there was only one child but not multiple or would it, explain further.

 

[vbaInet]

Show us some sample records from both tables. And tell us where you eventually want to display this information, i.e. a report?

 

[Mr. B]

ok, think this would work if there was only one child but not multiple or would it, explain further.

If your database if normalized then there is no reason that a query as I described in my post would return only one child record.

I also would like to see your data and the structure of your database.