Community Pick: Many members of our community have endorsed this article.
Editor's Choice: This article has been selected by our editors as an exceptional contribution.

Creating concatenated lists with reports

Published:
Updated:
It often makes sense to show lists as comma-separated items, especially in reporting. For example:

available colours: red, green, blue.
However, the reporting engine is biased towards tabular layout:

available colours:
red
green
blue
The latter is easy to achieve, even using only the report wizard. The former requires some programming.

The other day, I needed to produce a comma-separated list on a report, and was about to download from another article, Access & VB's Missing Domain Lookup Functions, the function DList(), designed for exactly this purpose. I was reluctant to add a module in this particular database, so I thought a little longer and realised that the report I had created already did 99% of the job.

Two lines of code are all that is needed to switch from the tablular layout to a list.


Reporting mechanics

report in design viewThe tabular layout uses a header, for example a group header if several item lists are displayed, and a detail section. The footer would be used to display sub-totals or to provide some white space at the end of each group. The sections are printed in sequence: header, detail(s), footer, header, details(s), ... Each group will have at least one detail section. However, depending on the record source and the data, there might be no item to display in a given group. There will be a detail section, but the fields meant to display the item will be Null.

The idea is to use the formatting events of these sections to collect the items and create a list for the group footer. As promised, this requires only two lines of code. This adds an item to the list:
    ItemList = ItemList+', ' & ItemName

Open in new window

The list needs to be reset for each group:
    ItemList = Null

Open in new window

This is so simple that it can actually be done using macros and the SetValue action. (It can be an embedded macro since Access 2007, but SetValue is not available in Sandbox mode, so the database must be “trusted”.)


Detailed example


In the attached tiny demo, I have created two tables called MainTable and LinkedTable. The main table contains names, and the linked table a list of colours associated to each name, the foreign key is `MainID´ referrencing `ID´, there is an additional `ItemNb´ to create a two-field key and incidentally a sort order for the colours.

Using the function DFind() mentioned in the introduction, I could get a list of colours associated to ID=1 like this:
? DList("ItemName","LinkedTable","MainID=1","ItemNb")

Open in new window

One solution would thus be to base the report on the main table only, and use this function to obtain the list of colours for each name. The last argument is the sort order; changing it to "ItemName" would produce an alphabetical list.

It is actually easier without the function. The report "Concatenation" is based on a query linking both tables, using a LEFT JOIN in order to show all records from the main table. A grouping is added, with both header and footer, on Main.ID. The detail section shows each ItemName, or Null if there are none to display. The sections have been renamed for readability; the text box collecting the list is called txtList, on the group footer.
Option Explicit
                      
                      Private Sub secDetail_Format(Cancel As Integer, FormatCount As Integer)
                          If FormatCount = 1 Then txtList = txtList + ", " & ItemName
                      End Sub
                      
                      Private Sub secMainHeader_Format(Cancel As Integer, FormatCount As Integer)
                          txtList = Null
                      End Sub

Open in new window

The `detail format´ event handler checks the format count argument. This is in order to avoid adding the same item twice if the reporting engine needs to reformat the same section for some reason. This precaution isn't really necessary, as the detail section will not be printed at all in the end.

In order to avoid adding a comma to an empty list, the operator `+´ is used. When one of the expressions on either side or the operator is Null, the entire expression becomes Null. This way, the comma will be added only between items, or rather only once the list is no longer Null.

After the grouping, there is also a sorting instruction. The sorting on `ItemNb´ is supposedly the intended sort order for this data; if `ItemName´ is used instead, the list will be alphabetised.

The last step is to make the detail section invisible. It needs to to contain a control displaying ItemName, which will no longer be printed, because only fields actually used on the report are available to the Visual Basic module. Notice that the format event occurs for invisible sections. (The detail section has been left visible in the demo file, change it to see the final intended result.)
ReportConcat.mdb
Note: The database in in Access 2000 format, but was created in Access 2007. After opening the database in an earlier version, if the demo doesn't work, please remove any missing reference: from Visual Basic Editor, choose (Tools | References) and uncheck any libraries marked as missing.


Conclusion


Reports without a visible detail section seem like a strange idea at first. I have used them a lot in classes, because they provide a simple way to execute total queries without opening the query editor at all. The reporting engine will create them in the background, based on the selected grouping(s) and the aggregate functions used in the controls. In many cases, the details are not important, so the detail section is simply hidden or reduced to zero height.

It had never occured to me how simple it was to create a concatenated list using a similar principle, and I hope this trick will be useful to others.


Happy coding!

Markus G Fischer
(°v°)

¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
This article is part of the EE-book unrestricted Access.
2
5,061 Views

Comments (1)

Author

Commented:
> It had never occurred to me...

Shortly after writing this article, I read an open question where that Asker wanted the “detail section of a report to print horizontally”. Two experts, als315 and boag2000, had already provided the exact solution detailed in this article, along with two others (using a concatenation function and using a columnar subreport).

It's like after you learn a new word: you seem to hear it everywhere. I searched some more and, sure enough, the technique has been described in Microsoft's knowledge base a decade ago, for example in: ACC2000: How to Concatenate Data from the 'Many' Side of a Relationship. My code is perhaps more concise, but only marginally better...

(°v°)

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.