ACC97: How to Combine Multiple Child Records into One Record (322863)

MORE INFORMATION

   Argument                   Description
   ---------------------------------------------------------------------

   strTblQryIn                The name of the table that contains the 
                              data that you want, or the name of the 
                              query that returns the data that you want. 
                              If you use a query, the query must not 
                              contain parameters.
  
   strFieldNameIn             The name of the field that contains the 
                              data that you want.

   strLinkChildFieldNameIn    The name of the field on which the 
                              child records link.

   varPKVvalue                A value from the field in the current 
                              record in the query.

   strDelimiter               The character that you want to delimit 
                              the results. If this argument is 
                              not supplied, the function uses a 
                              semicolon (;).
				

1. Open the Northwind.mdb sample database.
2. Add the following function to a global module:

   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
   					

3. Save the module, and then close it.
4. Click the Queries tab, and then click New.
5. Click OK, and then click Close to close the Show Table dialog box.
6. On the View menu, click SQL View.
7. In the SQL Editor, add the following code:

   SELECT Categories.CategoryID, Categories.CategoryName, 
   Categories.Description, CombineChildRecords("Products","ProductName","CategoryID",[CategoryID],",") AS ProductsList
   FROM Categories;
   					

8. Save the query as qryCombineProducts, and then run it.
   
   The ProductsList field contains all of the product names in a given category, separated by commas.