One to Many Relationship, Concatenate data

Posted on 2005-04-25
Last Modified: 2008-02-01

I am having some trouble concatenating data from the table on the many side of a one to many relationship.

I have two tables:

[EmpID] text, primary key (this is field filled with numbers, but is not used for any mathematical computation)
[FirstName] text
[LastName] text
{other fields that aren't used for this issue}

[DepID] AutoNumber, primary key
[EmpID] text

the two tables are used to show each employee's dependents.  Let me give an example:

0001 | John | Doe
0002 | Joe | Stump

1 | 0001 | Spouse | Jane
2 | 0001 | Child | Jim
3 | 0001 | Child | Shana
4 | 0002 | Spouse | Bess
5 | 0002 | Child | Bob

What I'd like to end up with is a query that will concatenate the [DepName] field on the many side of the table into a string.  Like so:

0001 | John | Doe | Jane, Jim, Shana
0002 | Joe | Stump | Bess, Bob.

I tried using the following code in a module, but when I called the function in this Query:
SELECT TblEmpInfo.*, fConcatFld("TblEmpDepend","EmpID","DepName","String",[EmpID]) AS DepNames
FROM TblEmpInfo;
I kept getting the "type mismatch" # 13 error

this is the code for the function (which I got from )

'************ Code Start **********
'This code was originally written by Dev Ashish
'It is not to be altered or distributed,
'except as part of an application.
'You are free to use it in any application,
'provided the copyright notice is left unchanged.
'Code Courtesy of
'Dev Ashish
Function fConcatChild(strChildTable As String, _
                    strIDName As String, _
                    strFldConcat As String, _
                    strIDType As String, _
                    varIDvalue As Variant) _
                    As String
'Returns a field from the Many table of a 1:M relationship
'in a semi-colon separated format.
'Usage Examples:
'   ?fConcatChild("Order Details", "OrderID", "Quantity", _
                "Long", 10255)
'Where  Order Details = Many side table
'       OrderID       = Primary Key of One side table
'       Quantity      = Field name to concatenate
'       Long          = DataType of Primary Key of One Side Table
'       10255         = Value on which return concatenated Quantity
Dim db As Database
Dim rs As Recordset
Dim varConcat As Variant
Dim strCriteria As String, strSQL As String
    On Error GoTo Err_fConcatChild
    varConcat = Null
    Set db = CurrentDb
    strSQL = "Select [" & strFldConcat & "] From [" & strChildTable & "]"
    strSQL = strSQL & " Where "
    Select Case strIDType
        Case "String":
            strSQL = strSQL & "[" & strIDName & "] = '" & varIDvalue & "'"
        Case "Long", "Integer", "Double":    'AutoNumber is Type Long
            strSQL = strSQL & "[" & strIDName & "] = " & varIDvalue
        Case Else
            GoTo Err_fConcatChild
    End Select
    Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

    'Are we sure that 'sub' records exist
    With rs
        If .RecordCount <> 0 Then
            'start concatenating records
            Do While Not rs.EOF
                varConcat = varConcat & rs(strFldConcat) & ";"
        End If
    End With
    'That's it... you should have a concatenated string now
    'Just Trim the trailing ;
    fConcatChild = Left(varConcat, Len(varConcat) - 1)
    Set rs = Nothing: Set db = Nothing
    Exit Function
    Resume Exit_fConcatChild
End Function

'************ Code End **********

Does anyone have any ideas as to what could be giving me the type mismatch error?  If so, any ideas as to how I could fix it?

Any help is greatly appreciated, as always.

Question by:wolfeman76
    LVL 11

    Expert Comment

    The only problem I can see is the name of the function you are calling should be


    and not


    your query should be as follows:

    SELECT TblEmpInfo.*, fConcatChild("TblEmpDepend","EmpID","DepName","String",[EmpID]) AS DepNames
    FROM TblEmpInfo

    But the error message suggests that one of your fields may not have the datatype you think it has.

    LVL 11

    Expert Comment

    Check that the datatype of EmpID really is text

    LVL 1

    Author Comment

    Um. Yeah.

    I found my mistake (aside from calling the wrong function)

    This database uses elements of ADO so the recordset needed to be declared a DAO recordset
    so instead of:
     Dim rs As Recordset

    It should have been
     Dim rs As DAO.Recordset

    stupid computers doing what they're told...Geez.

    Steve, Thanks for your help

    Accepted Solution

    PAQed with points refunded (250)

    Community Support Moderator

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
    I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now