merging separate records into one field for mail merge

[CES]

All,
I was hoping that someone might be able to give me some guidance in how to merge two records into one in a query. By that I mean in my table I have two records that have the same mailing address but are different records :
ID, First Name, Last Name, Address
1, Susan, Jones, 1 Main Street
2, Peter, Jones, 1 Main Street

What I’m trying to do is to accomplish a mail merge with Word 2003 where the resulting mailing label would look like this:

Susan Jones & Peter Jones
1 Main Street

What makes it even more difficult is that some addresses have more than two people per address so I can’t do a group by the Address field and then choose first record / last record.

Seems simple enough but I can’t figure out how to do it I would really appreciate any advice and guidance on how to accomplish this. Thanks in advance. - CES

 

[Ken Sheridan]

You can do it by concatenating the names into a sting expression in a VBA
function in a standard module, which loops through the rows for each address.
This assumes the addresses are all distinct of course. Really it would be
better if you split off the addresses into a separate table with a unique
AddressID column and referenced this with a foreign key AddressID column in
the main Addressees table. This has the added benefit of preventing errors
resulting from typos or differences such as 1 Main St in one row and 1 Main
Street in another.

However, with a single table, assuming unique addresses, no typos and the
use of the same conventions in all rows, it can be done with a function like
this:

Public Function GetAddressees(strAddress As String) As String

Dim rst As ADODB.Recordset
Dim strSQL As String
Dim strAddressees As String

strSQL = "SELECT [First Name], [Last Name] " & _
"FROM Addressees " & _
"WHERE Address = """ & strAddress & _
""" ORDER BY [Last Name],[First Name]"

Set rst = New ADODB.Recordset

With rst
.ActiveConnection = CurrentProject.Connection
.Open _
Source:=strSQL, _
CursorType:=adOpenKeyset, _
Options:=adCmdText

Do While Not .EOF
strAddressees = strAddressees & " & " & _
(.Fields("First Name") + " ") & .Fields("Last Name")
.MoveNext
Loop
.Close
' remove leading spaces and ampersand
strAddressees = Mid$( strAddressees, 4)
End With

Set rst = Nothing
GetAddressees = strAddressees

End Function

You can then call the function in a query like so, passing the value of the
Address column into it to return a single string of the addressees at each
address. the addressees would be listed in Last Name order, where there are
people with different last names at one address, and by First Name within
each last name.

SELECT DISTINCT
GetAddressees(Address) AS AddresseeList, Address
FROM Addressees;

Ken Sheridan
Stafford, England

 

[Duane Hookom]

There is a generic concatenate function with sample usage at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.