Many people at one address only get one letter addressed to all of them

[joe789]

Hi Folks,

I have a database, which is broken out by youth and adults for a non-profit governmental agency mandatory mailing. If the individual is a youth, I have created a salutation field "To the parents or legal guardian of: (Youth's First Name) (Youth's Last Name) at (Street Number and Street Name), (The City),(The State), (The ZipCode).” I have noticed that there are numerous incidences of more than one youth living at the same address in the system. Is it possible to somehow take all the youth's that are living in the same address and uniquely address them: for example:

To the parents or legal guardian of: Ann Apples, Frank Carrots, Ralph Pizza at 555 Seasame St. The City, The State, The Zip Code

As opposed to

To the parents or legal guardian of: Ann Apples at 555 Seasame St., The City, The State, The Zip Code

To the parents or legal guardian of: Frank Carrots at 555 Seasame St. The City, The State, The Zip Code

To the parents or legal guardian of: Ralph Pizza at 555 Seasame St. The City, The State, The Zip Code

Unfortunately the youth’s name must be in the salutation, and if somehow I can take all the youth’s names who live at the same address and add them all to the salutation, then only 1 mailing would have to go out per address regardless of whether multiple youth lived at that address.

Thank you very much in advance for suggesting how to do this and any help is grately appreciated,


Joe

 

[FoFa]

Yes, one way to do is to create a function that returns a string with name's in it already. Pass a parameter to the function so it can select all the names from your table, concatenate them together and pass them back, something like;
FUNCTION GiveMeDaNames (Key as WhatEverItIs)
Dim Work1 as string, strSQL as string
Work1 = ""
Put Sql to select only those you wish in strSQL
Create record set rs with rows
Do While NOT rs.EOF
if Work1 = "" then
Work1 = rs![First Name]
else
Work1 = Work1 & ", " & rs![First Name]
end if
rs.Movenext
LOOP
end recordset - DB connection
GiveMeDaNames = Work1
end function

You can them call this function in a query or whatever just passing the KEY to it for processing