SQL ACCESS QUERY GROUPING ON A COMMON FIELD 1

[tunsarod]

I have a table containing details of tenants. Each record has: fldPropertyID, fldTenantID, fldFirstname, fldLastname, etc.

I want to generate a mail-merge data source from this table using a query so that the output is grouped on the PropertyID field but with the tenant's names for each property combined together, as follows.

fldPropertyID | Tenant1.fldFirstname fldLastname Tenant2.fldFirstname fldLastname [Tenant3.fldFirstname fldLastname] etc

The idea is that in any letters to the tenants the combined tenants names could be displayed as a single merge field like so:

For the atten of: James Jones, Mary Smith, Roy Rogers and Peter Piper

It seems to me that the query needs a variable that can contain the names from successive rows within each group and then for that variable to be emptied at the start of each new row group. Or perhaps I should create a Public Function to concatenate the names for each PropertyID. I have been pondering this problem for several months and I haven't figured out a workable solution yet. Any help would be, well you know.

 

[BoxHead]

First, I'd like to express how happy I am to see that Roy Rogers and Peter Piper have finally settled their differences and are able to live together in harmony.

More to the point, the following function and command button were put into an unbound form.

The function moves through the records of a table that is limited by a query. The command button calls the function and displays the list of names in a textbox on the form. For this test, my criteria was an ID number greater than the number in another textbox (Text3).

Option Compare Database
Option Explicit
_________________________________
Private Function BuildString()

Dim db As Database
Dim rst As Recordset
Dim MyList As String
Dim MyCount As Integer
Dim i As Integer
Dim strSQL As String

strSQL = "SELECT distinct ID, Name FROM tblnamestring WHERE ID > " & Nz(Text3, 0) & ";"
MyList = ""

'Set the string to a zero length string to begin

Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL)
rst.MoveLast

'go to the end of the recordset to make sure all are counted

MyCount = rst.RecordCount

'determine the top number of For...Next loops

rst.MoveFirst

'go back to the first record before beginning to build the string

    For i = 1 To MyCount
        MyList = MyList & rst!Name & ", "
        rst.MoveNext
    Next i
    
BuildString = Left(MyList, Len(MyList) - 2)

'removes the extra comma at the end of the string

End Function
______________________________________
Private Sub Command2_Click()
BuildString
Text0 = BuildString
End Sub

______________________________________

For your purposes, you would change

     MyList = MyList & rst!Name & ", "

to

     MyList = MyList & fldFirstName & " " & fldLastName & ", "

and you would have to modify the SQL statement to fit your particular needs.


HTH John

Use what you have,
Learn what you can,
Create what you need.

 

[tunsarod]

Thanks John,

That was very helpful.

ps. Peter and Roy say hi!

Regards
Rod