Combine Fileds And Add "and"

Public Function getTradeMarks(varProdName As Variant) As String
Dim rs As DAO.Recordset
Dim intRecords As Integer
Dim strTradeMarks As String
Dim strSql As String

'strSql = "Select distinct [trademark] from tblTrademarks where [productName] = " & varProdName
strSql = "Select distinct [trademark] from tblTrademarks where [productName] = '" & varProdName & "'"
Set rs = CurrentDb.OpenRecordset(strSql, dbOpenDynaset)

Do While Not rs.EOF
intRecords = intRecords + 1
If rs.AbsolutePosition = 0 Then
strTradeMarks = rs.Fields("trademark")
Else
strTradeMarks = strTradeMarks & ", " & rs.Fields("trademark")
End If
rs.MoveNext
Loop
If intRecords = 1 Then
strTradeMarks = strTradeMarks & " is a registered trademark of company " & varProdName
Else
strTradeMarks = Left(strTradeMarks, InStrRev(strTradeMarks, ",") - 1) & " and" & Mid(strTradeMarks, InStrRev(strTradeMarks, ",") + 1) & " are registered trademarks of " & varProdName
End If
getTradeMarks = strTradeMarks
End Function




my input:

ID productName trademark
1 123 T1
2 123 T2
3 123 T3
4 234 T4
5 234 T1
6 123 T6
7 456 T7
8 789 T8

my output:

productName TradeMarks
123 T1, T2, T3 and T6 are registered trademarks of 123
234 T1 and T4 are registered trademarks of 234
456 T7 is a registered trademark of company 456
789 T8 is a registered trademark of company 789





The above code works Excellent.

I am looking for some code like this , my situation is similar but it has another column

For example In reference to the same example above I have one more column


Dose anyone know how to adjust the above code to get the following output


i.e.

my Input

ProductNumber TradeMark CompanyName

123 T1 C1
123 T2 C5
234 T4 C2
234 T1 C1
123 T6 C1
456 T3 C9
123 T12 C5
123 T9 C1
123 T4 C6


Want out put


productName TradeMarks
123 T1, T6 and T9 are registered trademarks of C1. T2 and T12 are registered trademarks of C5. T4 is registered trademark of C6.

234 T1 is registered trademark of C1. T4 is registered trademark of C2.

456 T3 is registered trademark of C9.