I have a DB with multiple records. (lets take a teacher student example)
Teacher (Tcode, Tname, Taddress)
Student (Scode, Sname, Sgrade)
I want to run a query that will give me
The result such as
QueryResult (TCode, Listof(Sname), Max(Sgrade))
The should be something like this:
ListOf(Sname) = john, sam, jenny, ann
Comam separated list of names
basically the number of records should be the number of Teachers.
I tred using this code i found online but it doesnt work in the sense when the cursor moves onto the 'concatnated field' the values change..
Expand|Select|Wrap|Line Numbers
- Public Function Concat2(strGroup As String, _
- strItem As String) As String
- Static strLastGroup As String
- Static strItems As String
- If strGroup = strLastGroup Then
- If InStr(", " & strItems & ", ", ", " & strItem & ", ") = 0 Then _
- strItems = strItems & ", " & strItem
- Else
- strLastGroup = strGroup
- strItems = strItem
- End If
- Concat2 = strItems
- End Function