<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Creating concatenated lists with reports

Published on
14,722 Points
4,022 Views
2 Endorsements
Last Modified:
Awarded
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
Comment
Author:harfang
1 Comment
LVL 58

Author Comment

by:harfang
> 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°)
0

Featured Post

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Join & Write a Comment

With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month