show only one record per unique identifier?

[Patchy]

I've been working on this issue for hours...and I can't get anywhere...

I have 10 tables linked in one query. 9 tables are linked to one main table (one to many relationship).

I want to get rid of duplicate records that show different data in multiple columns. I want only one record of this but retain with different data under different columns to be separated by commas.

For example: I want this...

Employee #               Name                  Course                      Start Date          Completion Date
1                      John Smith             MS Office Training            1/1/2010              5/1/2010
1                      John Smith             Python Training                1/30/2011            4/1/2011
1                      John Smith             Leadership Development     6/27/2013           9/1/2013
1                      John Smith             Sensitivity Training            9/5/2010                 -
2                      Hank Joel               MS Office Training             8/1/2010           10/1/2010
2                      Hank Joel               Sensitivity Training            8/1/2010            10/1/2010
2                      Hank Joel               WHMIS Training              11/15/2010           12/1/2010
3                      Jane Doe                Leadership Training           7/18/2011           9/26/2012

To turn like this:

Employee #               Name                  Course                                                                                                  Start Date                                                 Completion Date
1                      John Smith             MS Office Training, Python Training, Leadership Development, Sensitivity Training             1/1/2010, 1/30/2011, 6/27/2013, 9/5/2010                5/1/2010, 4/1/2011, 9/1/2013, -
2                      Hank Joel               MS Office Training, Sensitivity Training, WHMIS Training                                  8/1/2010, 8/1/2010, 11/15/2010                                                       10/1/2010, 10/1/2010, 12/1/2010
3                      Jane Doe                Leadership Training                                                                                 7/18/2011                                             9/26/2012

I am using two tables to find the data (main - "employee tbl" and "courses tbl")

I have been trying to follow Allen Browne`s method, but I`m unsuccessful.

This is the code I've put in SQL of this query:

SELECT [Employee #], ConcatRelated("[Courses]", "[Courses tbl]", "[Employee #] =" & [Employee #]) from [Employee Tbl]
SELECT [Employee #], ConcatRelated("[Start Date]", "[Courses tbl]", "[Employee #] =" & [Employee #]) from [Employee Tbl]
SELECT [Employee #], ConcatRelated("[Completion Date]", "[Courses tbl]", "[Employee #] =" & [Employee #]) from [Employee Tbl];

However, I am prompt with "Syntax error in query expression".

It also prompts another expression to be in error when I include the above but it runs okay when I don't do the above:

WHERE (([Employee Tbl].[Employee #]) Like [Enter Employee ID or leave blank for ALL employees] & "*")

I placed the following in VBA module:

Public Function ConcatRelated(strField As String, _
    strTable As String, _
    Optional strWhere As String, _
    Optional strOrderBy As String, _
    Optional strSeparator = ", ") As Variant
On Error GoTo Err_Handler
    'Purpose:   Generate a concatenated string of related records.
    'Return:    String variant, or Null if no matches.
    'Arguments: strField = name of field to get results from and concatenate.
    '           strTable = name of a table or query.
    '           strWhere = WHERE clause to choose the right values.
    '           strOrderBy = ORDER BY clause, for sorting the values.
    '           strSeparator = characters to use between the concatenated values.
    'Notes:     1. Use square brackets around field/table names with spaces or odd characters.
    '           2. strField can be a Multi-valued field (A2007 and later), but strOrderBy cannot.
    '           3. Nulls are omitted, zero-length strings (ZLSs) are returned as ZLSs.
    '           4. Returning more than 255 characters to a recordset triggers this Access bug:
    '               http://allenbrowne.com/bug-16.html
    Dim rs As DAO.Recordset         'Related records
    Dim rsMV As DAO.Recordset       'Multi-valued field recordset
    Dim strSql As String            'SQL statement
    Dim strOut As String            'Output string to concatenate to.
    Dim lngLen As Long              'Length of string.
    Dim bIsMultiValue As Boolean    'Flag if strField is a multi-valued field.
    
    'Initialize to Null
    ConcatRelated = Null
    
    'Build SQL string, and get the records.
    strSql = "SELECT " & strField & " FROM " & strTable
    If strWhere <> vbNullString Then
        strSql = strSql & " WHERE " & strWhere
    End If
    If strOrderBy <> vbNullString Then
        strSql = strSql & " ORDER BY " & strOrderBy
    End If
    Set rs = DBEngine(0)(0).OpenRecordset(strSql, dbOpenDynaset)
    'Determine if the requested field is multi-valued (Type is above 100.)
    bIsMultiValue = (rs(0).Type > 100)
    
    'Loop through the matching records
    Do While Not rs.EOF
        If bIsMultiValue Then
            'For multi-valued field, loop through the values
            Set rsMV = rs(0).Value
            Do While Not rsMV.EOF
                If Not IsNull(rsMV(0)) Then
                    strOut = strOut & rsMV(0) & strSeparator
                End If
                rsMV.MoveNext
            Loop
            Set rsMV = Nothing
        ElseIf Not IsNull(rs(0)) Then
            strOut = strOut & rs(0) & strSeparator
        End If
        rs.MoveNext
    Loop
    rs.Close
    
    'Return the string without the trailing separator.
    lngLen = Len(strOut) - Len(strSeparator)
    If lngLen > 0 Then
        ConcatRelated = Left(strOut, lngLen)
    End If

Exit_Handler:
    'Clean up
    Set rsMV = Nothing
    Set rs = Nothing
    Exit Function

Err_Handler:
    MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "ConcatRelated()"
    Resume Exit_Handler
End Function

 

[Brianwarnock]

Why on earth do you want to replace the easy to read first output with the difficult to read second.

When put into a report the first grouped on employee# and name will be even more clear and will not have problems with a variable number of courses.

Brian

 

[Patchy]

Why on earth do you want to replace the easy to read first output with the difficult to read second.

When put into a report the first grouped on employee# and name will be even more clear and will not have problems with a variable number of courses.

Brian

Because I am prompt with "too many fields" error when I use the group option from the report wizard... I want to show all the columns (about 80 in total) as I am trying to create a comprehensive report for every employee

 

[WayneRyan]

Patchy,

Try:

SELECT [Employee #], 
       ConcatRelated("[Courses]",         "[Courses tbl]", "[Employee #] =" & [Employee #]),
       ConcatRelated("[Start Date]",      "[Courses tbl]", "[Employee #] =" & [Employee #]),
       ConcatRelated("[Completion Date]", "[Courses tbl]", "[Employee #] =" & [Employee #]) 
from [Employee Tbl];

Wayne

 

[Patchy]

Patchy,

Try:

SELECT [Employee #], 
       ConcatRelated("[Courses]",         "[Courses tbl]", "[Employee #] =" & [Employee #]),
       ConcatRelated("[Start Date]",      "[Courses tbl]", "[Employee #] =" & [Employee #]),
       ConcatRelated("[Completion Date]", "[Courses tbl]", "[Employee #] =" & [Employee #]) 
from [Employee Tbl];

Wayne

I still get syntax error...so I tried making another query based on that query to clear the SQL view...

Now I get FROM clause error... I'm only using one table (the comprehensive table with everything in it) and I'm referencing this table when I entered the code you provided