"Advanced" report

[Kaganar]

Reader--

I'm having a bit of difficulty making a report which is to make a list of households and duplicate lines for those with last names. I've got this part done. The problem is, I need a field for each household which lists the first names of the members in the household in a horizontal layout to save paper. In the end it will be a list of 300 people, so a verticle layout is not acceptable. Each household has a record in a table called "Households" with an Autonumber (HouseholdID) as it's primary key. Each person has a record in the 'People' table with a foreign key of HouseholdID.

Forgive the following misformatting, as I'm not familiar with UBB Code:

Here's a simplified example with relevant fields shown:

Households:
HouseholdID CurrentlyActive
1           Y
2           Y
3           N

People:
First  Last   HouseholdID
Ian    Elliot 1
Joe    Elliot 1
Judy   Margia 1
Mel    Wilds  2
Scott  Elliot 3
Julia  Elliot 3

And the report would look like:

Family  Members                       Active
Elliot  Ian, Joe, Judy Margia         Y
Elliot  Scott, Julia                  N
Margia  Judy, Ian Elliot, Joe Elliot  Y
Wilds   Mel                           N

--Thank you,
--Ian Elliot

 

[PhilE]

You could read the query joined to both tables in code, & as a duplicate is read, then the first name could be added to a variable containing the already read entrie(s). When finding a different household/surname (i.e. onto next line in the report), write the variable string to a new table. Once complete, run the report on the new table.

 

[Kaganar]

Phil--

Thank you for your quick response. I am familiar with VB, but I'm not familiar with VBA. I will have to learn more about VBA before implimenting your suggestion.

--Thank you,
--Ian Elliot