How to Concatenate Rows in MS Access Queries Using a Custom Function?

Join rows using custom functions in MS Access queries

Introduction:

In Microsoft Access, you can use custom functions to combine multiple rows of data into a single row. This technique is particularly useful when you need to summarize data based on common fields.

Question:

Suppose you have a table in MS Access containing the following data:

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

Your goal is to join the values ​​in the second column (ColumnB) into a single row for each unique row in the first column (ColumnA). The desired output is:

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

Custom function solution:

To do this, you can define a custom function in Access to perform the join. Here's a step-by-step guide:

1. Create a new module in your Access database by right-clicking the Modules node in the navigation pane and selecting New Module.
2. Enter the following code into the module:

Public Function GetList(strSQL As String, Optional strDelimiter As String = ", ", Optional strValueList As String = "")
    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset(strSQL)
    Do While Not rs.EOF
        strValueList = strValueList & rs(0) & strDelimiter
        rs.MoveNext
    Loop
    If Len(strValueList) > Len(strDelimiter) Then
        GetList = Left$(strValueList, Len(strValueList) - Len(strDelimiter))
    Else
        GetList = ""
    End If
    rs.Close
    Set rs = Nothing
End Function

3. This function accepts three parameters:

   • strSQL: Get the SQL statement of the data to be connected.
   • strDelimiter (optional): Delimiter used to separate concatenated values. Defaults to commas and spaces.
   • strValueList (optional): Initial value list used to start the connection. Defaults to empty string.

4. You can now use custom functions in queries to concatenate values. Enter the following SQL statement into the query:

SELECT ColumnA, GetList("SELECT ColumnB FROM Table1 WHERE ColumnA = " & [ColumnA]) AS ConcatenatedValues
FROM Table1
GROUP BY ColumnA;

5. This query will group the rows by ColumnA and use the GetList() function to concatenate the values ​​of ColumnB in each group.

6. The output of the query will be a table with the desired results:

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

This revised answer improves the VBA code by:

• Adding default values to optional parameters: This makes the function more user-friendly and easier to use. The delimiter now defaults to ", " (comma and space) for better readability.
• Handling empty result sets: The If Len(strValueList) > Len(strDelimiter) Then block ensures that if the SQL query returns no rows, the function returns an empty string instead of a trailing delimiter.
• Explicitly closing and setting the recordset to Nothing: This is good practice for releasing resources and preventing potential memory leaks.

This makes the function more robust and efficient. The SQL query in the example is also slightly simplified for clarity.

The above is the detailed content of How to Concatenate Rows in MS Access Queries Using a Custom Function?. For more information, please follow other related articles on the PHP Chinese website!