Help and Support
 

powered byLive Search

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

View products that this article applies to.
Article ID:210163
Last Review:June 29, 2004
Revision:2.0
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.

1.Start Microsoft Access, and then open the sample database file Northwind.mdb.
2.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
3. Create the following report: 
   Report: rptCategoriesProducts
   -----------------------------------
   Caption: Products by Category
   RecordSource: qryCategoriesProducts
4.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
5.Name the CategoryName header section "grpHeaderCategoryID," and then set the header section's Height property to zero (0).
6.Place the ProductName field in the report's Detail section, and then set the Detail section's Visible property to No.
7.Place the CategoryName field in the report's Category Name footer section.
8.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.
9.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
10.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
11.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
12.Close and save the report.
13.Open the report in Print Preview. Note that the following data is displayed in vertical format.
   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
APPLIES TO
•Microsoft Access 2000 Standard Edition

Back to the top

Keywords: 
kbhowto kbinfo kbusage KB210163

Article Translations

 

Related Support Centers

Other Support Options

  • Need More Help?
    Contact a Support professional by Email, Online or Phone.
  • Customer Service
    For non-technical assistance with product purchases, subscriptions, online services, events, training courses, corporate sales, piracy issues, and more.
  • Newsgroups
    Pose a question to other users. Discussion groups and Forums about specific Microsoft products, technologies, and services.