2

I need to view results of the query as a string with delimiter.

Example:

Table "Cars"

carId | carName
1 | Honda
2 | Ford

Table "Drivers"

driverId | driverName
1 | John

Table "Timetable"

tDate | tDriver | tCar
15/07/2014 | 1 | 1
15/07/2014 | 1 | 2

Query "UsedCars"

driver | car
1 | 1
1 | 2

I need the results in a query "UsedCars" to look like this:

driver | car
1 | 1;2

Any help will be much appreciated.

Erik A
  • 31,639
  • 12
  • 42
  • 67
user3844875
  • 21
  • 1
  • 2
  • Thanks everyone. Found working solution here: http://stackoverflow.com/questions/17350055/concatenate-rows-in-microsoft-access – user3844875 Jul 26 '14 at 05:26

2 Answers2

2

This is like a GROUP CONCAT in mySQL which is not available in MS Access. But there is a work around wherein you create a Function (via Module) in MS Access named GetList for example, so that you will have this query:

SELECT Driver, GetList("SELECT Car FROM UsedCars","",";") as Cars
FROM UsedCars
GROUP BY Driver

The Function using VBA is the one below:

Option Compare Database

Public Function GetList(SQL As String _
                        , Optional ColumnDelimeter As String = ", " _
                        , Optional RowDelimeter As String = vbCrLf) As String
'PURPOSE: to return a combined string from the passed query
'ARGS:
'   1. SQL is a valid Select statement
'   2. ColumnDelimiter is the character(s) that separate each column
'   3. RowDelimiter is the character(s) that separate each row
'RETURN VAL: Concatenated list
'DESIGN NOTES:
'EXAMPLE CALL: =GetList("Select Col1,Col2 From Table1 Where Table1.Key = " & OuterTable.Key)

Const PROCNAME = "GetList"
Const adClipString = 2
Dim oConn As ADODB.Connection
Dim oRS As ADODB.Recordset
Dim sResult As String

On Error GoTo ProcErr

Set oConn = CurrentProject.Connection
Set oRS = oConn.Execute(SQL)

sResult = oRS.GetString(adClipString, -1, ColumnDelimeter, RowDelimeter)

If Right(sResult, Len(RowDelimeter)) = RowDelimeter Then
   sResult = Mid$(sResult, 1, Len(sResult) - Len(RowDelimeter))
End If

GetList = sResult
oRS.Close
oConn.Close

CleanUp:
  Set oRS = Nothing
  Set oConn = Nothing

Exit Function

ProcErr:
' insert error handler
 Resume CleanUp

End Function

Source here

Community
  • 1
  • 1
Edper
  • 9,144
  • 1
  • 27
  • 46
0

have you tried concatenation?

http://www.techonthenet.com/access/functions/string/concat.php

Thomas
  • 366
  • 6
  • 19
  • The OP is looking not simply for concatenation but `GROUP BY` whose grouped values are concatenated. – Edper Jul 16 '14 at 12:52
  • may you are right, my first answer isnot right, but group by wont solve that problem. There would be needed something like string_agg() func in postgresql... – Thomas Jul 16 '14 at 13:03
  • try that search: ms+access+convert+array+to+string – Thomas Jul 16 '14 at 13:06
  • Actually this one worked for me: http://stackoverflow.com/questions/17350055/concatenate-rows-in-microsoft-access – user3844875 Jul 26 '14 at 05:25