Home >Database >Mysql Tutorial >How to Concatenate Rows in MS Access Queries Using a Custom Function?

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

Barbara Streisand
Barbara StreisandOriginal
2025-01-07 21:33:42262browse

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:
<code class="language-vba">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</code>
  1. 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.
  2. You can now use custom functions in queries to concatenate values. Enter the following SQL statement into the query:

<code class="language-sql">SELECT ColumnA, GetList("SELECT ColumnB FROM Table1 WHERE ColumnA = " & [ColumnA]) AS ConcatenatedValues
FROM Table1
GROUP BY ColumnA;</code>
  1. This query will group the rows by ColumnA and use the GetList() function to concatenate the values ​​of ColumnB in each group.

  2. 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!

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn