How Can I Concatenate Multiple Rows into a Single Value in an MS Access Query?

Combining Multiple Rows into a Single Value in MS Access Queries

This guide demonstrates two methods for concatenating multiple rows into a single value within an MS Access query, based on a shared field. The methods use either a custom function or built-in aggregate functions.

Method 1: Using a Custom Function (GetList)

This approach requires creating a VBA function. The query then calls this function to perform the concatenation.

Here's the query structure:

<code class="language-sql">SELECT ColumnA, GetList("SELECT ColumnB FROM Table1 WHERE ColumnA = " & [ColumnA], "", ", ") AS ColumnB
FROM Table1
GROUP BY ColumnA;</code>

The GetList function (defined in a VBA module) iterates through rows sharing the same ColumnA value and concatenates their corresponding ColumnB values, using a comma as the delimiter. You can easily change this delimiter.

Method 2: Using the Built-in List Aggregate Function

This method leverages the built-in List function for a simpler, code-free solution.

The query is:

<code class="language-sql">SELECT ColumnA, List(ColumnB) AS ColumnB
FROM Table1
GROUP BY ColumnA;</code>

The List function automatically concatenates ColumnB values for each unique ColumnA value. Note that the delimiter used by List might vary depending on your Access settings.

Illustrative Data and Results

Consider this sample data:

ColumnA	ColumnB
1	abc
1	pqr
1	xyz
2	efg
2	hij
3	asd

Both methods will produce the following result:

ColumnA	ColumnB
1	abc, pqr, xyz
2	efg, hij
3	asd

Customizing the Delimiter

Adjust the delimiter within the GetList function's parameters (Method 1) or potentially through regional settings (Method 2) to use a different separator (e.g., semicolon, space).

The above is the detailed content of How Can I Concatenate Multiple Rows into a Single Value in an MS Access Query?. For more information, please follow other related articles on the PHP Chinese website!