Family grouping records for mailing labels 1

[DaveLamb]

I have an Access 2002 database of people (both students and volunteers) involved in an after-school program. I need to create mailing labels for people with certain characteristics. Seemingly a simple operation, but I run into the issue of creating more than one label for the same address if two or more people who meet my selection criteria live there.

An example (and my immediate need): making labels for volunteers who are "table parents" (tables!Role!TableParent). People can have more than one "role". The person table (Person) and the role table (Role) have a one-to-many relationship (one person, many roles). The link is a PersonID field on the Role table. Other possible roles are "student", "teacher", etc.

People (tables!Person) are also linked to households (tables!Household) in a many-to-many relationship via a bridge table (tables!Relationships) which contains the primary key for both Person and Household. The household table has the address and phone number for everyone who lives there. This information is not in the Person table. To make labels, I created a query to link these tables together. The problem is, many of the table parents are husband and wife. My query results in a separate mailing label for the husband and the wife. It would be much better to make one label for each household where there is one or more people meeting the selection criteria (in this case, that is 'role="tableparent"'). This will also be an issue with labels for students and very likely other groups.

I found a previous post that seemed to send me in the right direction: PaulBricker's excellent reply to awinger on 10/19/02. Subject was "query construction for a mailing label." However, I've had trouble adapting awinger's solution for my needs.

I use VB quite a bit for programming forms, but am inexperienced with handling record sets. This is at least part of my problem. The Do Loop suggested by PaulBricker seems like a good solution, but I haven't been able to properly create or open the recordset I need in order to try out the Do Loop.

I hope I've sketched out the problem completely. I would be most grateful if someone could point me in the right direction.

Many thanks,

Dave Lamb

 

[dhookom]

Wouldn't a totals query work that groups by the fields in your Household table? You don't have to show fields from the Person table or just set them to First.

Duane
MS Access MVP

 

[DaveLamb]

Thanks Duane. That works nicely to pull out just one row per address. I wasn't clear about the desired outcome. I want to create a label that includes all of the appropriate names as well. Thus, I have one record for David Lamb, another for Cheryl Lamb. They are both linked to the same houshold.

Your query creates a label just for David (with the "total" property of the FirstName field set to First). The goal is to create one mailing label addressed to David and Cheryl Lamb. By the way, I do have the following fields in the Person table: FirstName, LastName, SortName and MailName.

Dave

 

[dhookom]

Have you thought out what this will look like with more than 2 persons or different last names? You can create a totals query with a "concatenated" expression of "child" records. You could search google groups on hookom and "Function Concatenate(" for the code. If you don't find this or have questions, come on back.

Duane
MS Access MVP

 

[DaveLamb]

Incredible. I dropped your code into a module as-is. I'm no SQL expert, but managed to modify your sample SQL properly. It worked the first time.

Many thanks, Duane!

Dave Lamb

 

[dhookom]

Great work! Most threads, I have to write the SQL and all the expressions.

Duane
MS Access MVP