Home >Database >Mysql Tutorial >How to Concatenate Rows in MS Access Queries Using a Custom Function?
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:
<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>
This function accepts three parameters:
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>
This query will group the rows by ColumnA and use the GetList() function to concatenate the values of ColumnB in each group.
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:
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.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!