Microsoft KB Archive/322863

= ACC97: How to Combine Multiple Child Records into One Record =

Article ID: 322863

Article Last Modified on 2/1/2007

-

APPLIES TO


 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q322863



Advanced: Requires expert coding, interoperability, and multiuser skills.

For a Microsoft Microsoft Access 2002 version of this article, see 318642.

For a Microsoft Microsoft Access 2000 version of this article, see 322813.



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.



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:  Open the Northwind.mdb sample database.  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(&quot;&quot;)

If IsMissing(strDelimiter) Then strDelimiter = &quot;; &quot; strSQL = &quot;SELECT [&quot; & strFieldNameIn & &quot;] FROM [&quot; & strTblQryIn & &quot;]&quot; qd.SQL = strSQL & &quot; WHERE [&quot; & strLinkChildFieldNameIn & &quot;] = [ParamIn]&quot; qd.Parameters(&quot;ParamIn&quot;).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  Save the module, and then close it. Click the Queries tab, and then click New. Click OK, and then click Close to close the Show Table dialog box. On the View menu, click SQL View.</li>  In the SQL Editor, add the following code: SELECT Categories.CategoryID, Categories.CategoryName, Categories.Description, CombineChildRecords(&quot;Products&quot;,&quot;ProductName&quot;,&quot;CategoryID&quot;,[CategoryID],&quot;,&quot;) AS ProductsList FROM Categories; </li> 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.</li></ol>

<div class="references_section">