Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. ACC2000: How to Combine Multiple Child Records into One Record

ACC2000: How to Combine Multiple Child Records into One Record

View products that this article applies to.

This article was previously published under Q322813
Advanced: Requires expert coding, interoperability, and multiuser skills.

For a Microsoft Microsoft Access 2002 version of this article, see 318642� (http://support.microsoft.com/kb/318642/EN-US/ ) .
For a Microsoft Microsoft Access 97 version of this article, see 322863� (http://support.microsoft.com/kb/322863/EN-US/ ) .

↑ Back to the top

Summary

This article shows you how to combine multiple child records into one record. In the following example, which uses the Northwind.mdb sample database, the SQL query passes the category name to the CombineChildRecords function. The function creates a category-specific Recordset object that is based on the Products table, and then parses the Recordset object to generate a comma-delimited list of products.

↑ Back to the top

More information

The CombineChildRecords function accepts the following arguments. 
   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 (;).

To combine all of the product names from each category into one field, follow these steps:
  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.

↑ Back to the top

Keywords: KB322813, kbhowto

↑ Back to the top

Article Info
Article ID : 322813
Revision : 2
Created on : 6/25/2004
Published on : 6/25/2004
Exists online : False
Views : 259