Microsoft KB Archive/210163

= ACC2000: How to Concatenate Data from the 'Many' Side of a Relationship =

Article ID: 210163

Article Last Modified on 6/29/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q210163



Moderate: Requires basic macro, coding, and interoperability skills.



SUMMARY
This article provides a technique to concatenate information from multiple records based on the "many" side of a relationship, and to format the results into a single line separated by commas in a report.



MORE INFORMATION
To concatenate a list of items from a table on the "many" side of a relationship, follow these steps:

CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

 Start Microsoft Access, and then open the sample database file Northwind.mdb.  Create the following query as the basis for the sample report:   Query: qryCategoriesProducts -  Type: Select Query Join: Categories.[CategoryID] <-> Products.[CategoryID] Field: CategoryName Table: Categories Sort: Ascending Field: ProductName Table: Products Sort: None   Create the following report:   Report: rptCategoriesProducts ---  Caption: Products by Category RecordSource: qryCategoriesProducts   Create the following Sorting and Grouping sections in the report:   Field Expression: CategoryName Sort: Ascending Group Header: Yes Group Footer: Yes

Field Expression: ProductName Sort: Ascending Group Header: No     Group Footer: No                     Name the CategoryName header section "grpHeaderCategoryID," and then set the header section's Height property to zero (0).</li> Place the ProductName field in the report's Detail section, and then set the Detail section's Visible property to No.</li> Place the CategoryName field in the report's Category Name footer section.</li> Add an unbound text box named AllProducts to the report's Category Name footer section.

NOTE: You may want to set the CanGrow property of the AllProducts field to Yes if you anticipate that the number of items may exceed the width of the text box. This enables the text box to grow vertically.

NOTE: Place the CategoryName and AllProducts fields side by side for clarity when you print the report.</li>  Add the following lines to the report's Declarations section if they are not already there: Option Compare Database ' Use database order for string comparisons. Option Explicit Dim FirstPass As Integer </li>  Add the following code to the CategoryName header section's OnFormat property: Sub grpHeaderCategoryID_Format (Cancel As Integer, FormatCount As Integer) Me!AllProducts = Null FirstPass = False

End Sub </li>  Add the following code to the Detail section's OnFormat property: Sub Detail_Format (Cancel As Integer, FormatCount As Integer) On Local Error GoTo Detail_Format_Err If Not FirstPass Then Me!AllProducts = Me![ProductName] FirstPass = True Else Me!AllProducts = Me!AllProducts & ", " & Me![ProductName] End If Detail_Format_End: Exit Sub Detail_Format_Err: MsgBox Error$ Resume Detail_Format_End End Sub </li> Close and save the report.</li>  Open the report in Print Preview. Note that the following data is displayed in vertical format. <pre class="fixed_text">  Category: Beverages Products:  Chai, Chang, Chartreuse verte, Cote de Blaye, Guarana Fantastica, Ipoh Coffee, Lakkalikoori, Laughing Lumberjack Lager, Outback Lager, Rhonbrau Klosterbier, Sasquatch Ale, Steeleye Stout

Category: Condiments Products: Aniseed Syrup, Chef Anton's Cajun Seasoning, Chef Anton's Gumbo Mix, Genen Shouyu, Grandma's                                 Boysenberry Spread, Gula Malacca, Louisiana Fiery Hot Pepper Sauce, Louisiana Hot Spiced Okra, Northwoods Cranberry Sauce, Original Frankfurter grune Sobe, Sirop d'erable, Vegie-spread

</li></ol>

Additional query words: horizontal

Keywords: kbhowto kbinfo kbusage KB210163

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.