Ms Access SQL: Concatenate seperated by commas one to many relationship

Question

With the following kind of table:

tblRequest
-RequestID(double)
-RequestDescription (String)


tblError
-ErrorID(long integer)
-ErrorName(String)
-RequestID(double)

The above relationship is a ONE to MANY relationship.

I want to VIEW the data in the following manner. Therefore, I need a SELECT query which displays the data in the following manner.

Request          Error(s)
1                Error1, Error6
2                Error2, Error3
3.4              Error4, Error2, Error1

I tried to search for an answer which involved FOR XML PATH('')). However, I do not think it can work in Ms-Access.

Answer 1

Here's a potential solution.

Step 1:

Create this function in your MS Access App.I don't think this is the most efficient solution, however it should work well enough if the number of records isn't very large.

Public Function getErrorText(ByVal MyId As Double) As String
    Dim myrs        As Recordset

    'Create the recordset
    Set myrs = CurrentDb.OpenRecordset("select distinct ErrorID from tblError where RequestID = " & MyId)

    'Build the error string
    Do Until myrs.EOF
        getErrorText = myrs.Fields(0).Value & ", " & getErrorText
        myrs.MoveNext
    Loop

    'Clean up
    myrs.Close
    Set myrs = Nothing

    'Return the correct cleaned up string
    getErrorText = Left(getErrorText, Len(getErrorText) - 2)
End Function

Step 2:

You should then be able to run the following SQL statement to get the output desired.

SELECT distinct tblError.RequestID, getErrorText( tblError.[RequestID]) AS [Error(s)]
FROM tblError INNER JOIN tblRequest ON tblError.RequestID = tblRequest.RequestID
WHERE (((getErrorText( tblError.[RequestID])) Is Not Null));