explains how to do what I need, sort of…but it gives this SQL statement:
SELECT Categories.CategoryID, Categories.CategoryName,
Categories.Description, CombineChildRecords(“Products”,“ProductName”,“CategoryID”,[CategoryID],",") AS ProductsList
FROM Categories;
It works perfectly in the Northwinds sample database, but the problem I’m having is converting it to work in a different database, since I’m not positive exactly what everything in the statement means. Specifically, it’s this part that doesn’t seem to work if I change anything at all:
(“Products”,“ProductName”,“CategoryID”,[CategoryID],",")
Does anyone know specifically what each of those represents? Especially the [CategoryID]—why is it the only part in brackets? . I had no trouble getting it to run in the sample database, but I need to convert it to a different database and I can’t seem to make the appropriate changes without getting an error.
SELECT Categories.CategoryID, Categories.CategoryName,
Categories.Description, CombineChildRecords("Products","ProductName","CategoryID",[CategoryID],",") AS ProductsList
FROM Categories;
isn’t really just plain old SQL. What you need to understand is that earlier in the linked document a function was defined. That function is called “CombineChildRecords”.
The function definition happens here:
Function CombineChildRecords(strTblQryIn As String, _
strFieldNameIn As String, strLinkChildFieldNameIn As String, _
varPKVvalue As Variant, Optional strDelimiter) As Variant
Dim db As DAO.Database
Dim qd As DAO.QueryDef
Dim rs As DAO.Recordset
Dim strSQL As String
Dim varResult As Variant
Set db = CurrentDb
Set qd = db.CreateQueryDef("")
If IsMissing(strDelimiter) Then strDelimiter = "; "
strSQL = "SELECT [" & strFieldNameIn & "] FROM [" & strTblQryIn & "]"
qd.SQL = strSQL & " WHERE [" & strLinkChildFieldNameIn & "] = [ParamIn]"
qd.Parameters("ParamIn").Value = varPKVvalue
Set rs = qd.OpenRecordset()
Do Until rs.EOF
varResult = varResult & rs.Fields(strFieldNameIn).Value & strDelimiter
rs.MoveNext
Loop
rs.Close
If Len(varResult) > 0 Then varResult = Left$(varResult, _
Len(varResult) - 2)
CombineChildRecords = varResult
Set rs = Nothing
Set qd = Nothing
Set db = Nothing
End Function
If you look at this statement (from above);
Function CombineChildRecords(strTblQryIn As String, _
strFieldNameIn As String, strLinkChildFieldNameIn As String, _
varPKVvalue As Variant, Optional strDelimiter) As Variant
you’ll see where each of those parameters are defined. Following the rest of the code will tell you what Access (in this case it’s VBA) does with each of those values to generate the output.
Try pulling apart the function to see what it actually does. If you get stuck, post back and I’m sure someone will be able to help (sorry but I don’t have time right now to go through the function line by line).