Mailing Labels: Many people, one address

[SharonC]

Please find attached a picture of the relationships I've established for my civic league database.

My problem is that there can be more than two people (with different last names) living at the same address. I would like to be able to create mailing labels for one address while addressing all names residing there.

Name1
Name2
Name3.....
Address

I've read and understand Microsoft KB 207753 dtd 6/24/2004 which details how to create mailing labels for TWO people living at the same address, but how would you create a report (label) for more than two?

This is a many-to-one problem, isn't it? Or am I just to thick-headed to see the obvious?

Any help appreciated.

 

[grnzbra]

Do you have a maximum number of people you will put on a label?

Here's an off the top of my head thought.

Create a query that gives you the name and address id, sorted by address id.

Create a sub in which you open a recordset from the query.
(oh yes, in addition to the db and recordset declarations, declare a variable for the address id and a string variable to hold names)

Go to the first record and start a

Do While Not rs.EOF

set a variable equal to the address ID

Create another loop of the configuration

Do While rs!AddressID = Variable

stringvariable = stringvariable & rs!Name & Chr(10) & Chr(13)

rs.movenext

Loop

Open a recordset to a temporary table and do a

rs2.addnew

rs2.Name = Left(stringvariable, Len(stringvariable) - 14)
rs2.Address ID = Variable
rs.update
stringvariable = ""
variable = rs!Address ID
Loop


This should give you a table with the address ID and the names separated by a carriage return and line feed. Set the Can grow property of the name field on your labels, and it should give you the names stacked above each other along with the address brought in by the link to the address table.

 

[SharonC]

Thanks for the quick comeback!

You're quite right. What if I had 10 people under one roof trying to smash them together on one label?!

At the moment, the maximum I have is three people at one address. I guess I'll just resort to addressing the label as: "To the Residents of..."

No big deal...just thought I had incorrectly set up my relationships.

Peace & thanks again!

 

[grnzbra]

NO! NO! NO!
Don't give up so easily. Do you understand what's going on in the loops in the code?
To limit to three, you would just need to modify it to deal with a limit of 3 per address ID. I think it would involve another loop counting to three.