access sql query to concatenate rows

Question

I have following data in csv format which I exported in access format

House Number | Road name | Postcode
1 | alex road | sw2 4r
2 | alex road | sw2 4r
3 | alex road | sw1 2b
4 | alex road | sw2 4r
5 | alex road | sw2 4r
7 | brit road | bw2 4t
4 | brit road | bw2 4t
6 | brit road | bw2 4t
8 | brit road | bw2 4t
10 | brit road | bw2 4t

I need a ACCESS SQL query which can group all records by road name, total records can be more than 20,000

I need output in following format

Road Name | House Number | Postcode
--------------------------------------
alex road | 1,2,3,4,5 |  sw2 4r,sw1 2b
brit road | 7,4,6,8,10 | bw2 4t

You are looking for something like `group_concat` but in MS Access try this: http://stackoverflow.com/questions/2852892/is-there-a-group-concat-function-in-ms-access — Michał Powaga, Dec 22 '11 at 10:26

score 1 · Answer 1 · answered Dec 22 '11 at 11:29

As was pointed out by onedaywhen in an early post on SO, this is easier with ADO, there is an example here

Sample query:

   SELECT d.DeptID, d.Department, 
          ConcatADO("SELECT FName & ' ' & SName, Address FROM Persons 
                     WHERE DeptID=" & [d].[DeptID],", "," : ") AS Who
   FROM Departments AS d INNER JOIN Persons AS p ON d.DeptID = p.DeptID
   GROUP BY d.DeptID, d.Department, 3;

Function using ADO

Function ConcatADO(strSQL As String, strColDelim, strRowDelim, ParamArray NameList() As Variant)
   Dim rs As New ADODB.Recordset
   Dim strList As String

   On Error GoTo Proc_Err

       If strSQL <> "" Then
           rs.Open strSQL, CurrentProject.Connection
           strList = rs.GetString(, , strColDelim, strRowDelim)
           strList = Mid(strList, 1, Len(strList) - Len(strRowDelim))
       Else
           strList = Join(NameList, strColDelim)
       End If

       ConcatADO = strList

   Exit Function

   Proc_Err:
       ConcatADO = "***" & UCase(Err.Description)
   End Function

score 0 · Answer 2 · answered Dec 22 '11 at 11:33

because there is no such function like group_concat in Access. so I import my csv file into phpMyAdmin mysql database using its built in import tool and use following query to group them

SELECT  road,pcode, group_concat(house_number) 
FROM mytable
GROUP BY road, pcode

and then later export it back to csv using phpmyadmin built in export tool. I don't know if there is a better way of doing this.

access sql query to concatenate rows

2 Answers2

Linked