Combine Fields

[rpatil]

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.