UDF returning a concatenated list of sub-record values

[Victor70]

Dear experts,

I have multiple records associated with the same person (name_id is a unique key per person). I need to create a list with one row per person and all records concatenated in that row.

PersonA Record1
PersonA Record2
PersonB Record3
PersonB Record4
PersonC Record5

into

PersonA Record1; Record2
PersonB Record3; Record4
PersonC Record5

I tried to use a UDF to return a concatenated list of sub-record values that I found on the web. With my limited understanding I plugged my variables into the query with the UDF but it returns initial multiple rows per person and blank second column where I would expect to have my combined records.

What am I doing wrong here?
Thanks a lot for any feedback!

SELECT [name_id], fConcatChild("TableName","name_id","Records","String",10255) AS SubFormValues
FROM [TableName];

UDF

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
'
' Set a reference to DAO

Dim db As DAO.Database
Dim rs As DAO.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) & ";"
                .MoveNext
            Loop
        End If
    End With
        
    'That's it... you should have a concatenated string now
    'Just Trim the trailing ;
    fConcatChild = Left(varConcat, Len(varConcat) - 1)
        
Exit_fConcatChild:
    If Not rs Is Nothing Then
        rs.Close
        Set rs = Nothing
    End If
    Set db = Nothing
    Exit Function
    
Err_fConcatChild:
    Resume Exit_fConcatChild
End Function

 

[vbaInet]

Victor,

Have a look at the following url. It gives a better explanation of how to use the function provided:

http://allenbrowne.com/func-concat.html

Note, this is a different function from the one you currently have.

 

[Victor70]

Thank you very much for such a prompt response.

When I used the UDF, I am still getting multiple rows (per same person exactly as I have in the feeding query [DVS - ACTW Persons]), the records are concatenated but repeated every row .

The fields of my query are:
name_id, Person's Name, Record

The code I am using follows:

SELECT [DVS - ACTW Persons].[Person's Name], ConcatRelated("Record","[DVS - ACTW Persons]","name_id=" & [name_id]) AS SubFormValues
FROM [DVS - ACTW Persons];

1. How can I get rid of duplicate rows?
2. How do I go about setting the second optional variable while omitting the first optional variable in my UDF? What is the syntax required for that?

Many thanks!

 

[vbaInet]

I thought you were getting errors in your other code.

So what you need to do is put the code in a query that pulls data from the Persons table, not the [DVS - ACTW Persons]. The Persons table is a list of individuals and obviously that's distinct.

By the way, spaces in names are not good.

 

[Victor70]

Yes, I was getting errors with the previous code, but I used the one you suggested.

The [DVS - ACTW Persons] query has a listing of persons and specific information pertaining to those persons (several records per person in most of the cases).

The instructions to the code provided by you say that it can either a table or a code. If I go to the initial Persons table only, I won't get the records. So, can I refer to my query only, or do I have to include both the Persons table and my query into the formula?

I know, spaces are bad, you told me that before. Next time I'll do better.

 

[vbaInet]

If you re-read my previous post you will understand what I'm talking about. Use [DVS - ACTW Persons] in the code I gave you but in the SQL statement refer to the Persons table:

SELECT [Person's Name], ConcatRelated("Record","Persons","name_id=" & [name_id]) AS SubFormValues
FROM [COLOR=Blue]Persons[/COLOR];

Does that make sense?

 

[Victor70]

vbaInet, you got me totally confused. Sorry about that. I really do not understand how I can use my query in the code and the Persons table in the SQL statement. I thought the code is a function that I am using in the SQL, and I am making references to tables/queries in the SQL code only.

If you allow me, I would like to reiterate a bit.
My query produces the following output. It pulls data from 2 tables to filter out persons that do not match my criteria.
name_id PersonA Record1
name_id PersonA Record2
name_id PersonB Record3
name_id PersonB Record4
name_id PersonC Record5

I need to convert that into this:

PersonA Record1; Record2
PersonB Record3; Record4
PersonC Record5

I thought that the whole point of concatenation function is to convert the query results into the desired layout?
I guess I am not that smart...

 

[vbaInet]

Just do as you've been advised and see the result!

 

[Victor70]

It did not work and, frankly, I cannot see how it would

As I said, I do not have Records in the Name table. By referring to only the initial Name table (dbo_nmmain in my case) I can only get lname, fname, mname, suffix. There is another table where I have Records (table dbo_wamain), which links to the first table via name_id.

In the code that follows I do not make any reference to dbo_wamain, and, therefore, cannot get anything from the Records associated with particular person.

SELECT [lname], [fname], [mname], ConcatRelated("[COLOR="Red"]Record[/COLOR]","dbo_nmmain","name_id=" & [name_id]) AS SubFormValues
FROM dbo_nmmain;

 

[vbaInet]

My mistake Victor. I had it in my head but mistakenly changed the table name in the ConcatRelated function. Use the following:

SELECT [lname], [fname], [mname], ConcatRelated("[COLOR=Red]Record[/COLOR]","[DVS - ACTW Persons]","name_id=" & [name_id]) AS SubFormValues
FROM dbo_nmmain;

 

[Victor70]

Thanks for your help.

It worked but...it gave my the entire list of all names in the dbo_nmmain (334K). Obviously some of them have records, most - do not.

I need somehow to filter out all the names (about 332K in my case) without Records.

 

[vbaInet]

Create a query based on dbo_nmain and put in the necessary criteria. Then run the function against this query.

 

[Victor70]

This was in fact my initial try with the UDF suggested by you.
I was using my query. The results of this SQL statement: full name, same number of rows per person (depending on the number of records per person) and concatenated records (concatenation worked fine) in separate field.
However, I would expect it to have only one row per person with concatenated records.

Am I not sure if this is correct - "name_id=" & [name_id] (or I should say, I do not understand this at all)

SELECT [DVS - ACTW Persons].[Person's Name], ConcatRelated("Record","[DVS - ACTW Persons]","name_id=" & [name_id]) AS SubFormValues
FROM [DVS - ACTW Persons];

Many thanks.

 

[vbaInet]

This was in fact my initial try with the UDF suggested by you.
Am I not sure if this is correct - "name_id=" & [name_id] (or I should say, I do not understand this at all)

No it wasn't. In your original thread your query produces repeated records per Person. That wasn't what I advised you in my last two posts.

Where in the link provided did you see something like this "name_id=" & [name_id] done?

 

[Victor70]

Third argument of the function in the link has it - "CompanyID = " & [CompanyID]

Do I have to omit this?

 

[vbaInet]

I didn't write the function Victor. All explanations re the function are in the link. It says:

3. Thirdly, supply the filter to limit the function to the desired values. This will normally be of the form:
"[ForeignKeyFieldName] = " & [PrimaryKeyFieldName]
If the foreign key field is Text (not Number), include quote marks as delimiters, e.g.:
"[ForeignKeyFieldName] = """ & [PrimaryKeyFieldName] & """"
For an explanation of the quotes, see Quotation marks within quotes.
Any valid WHERE clause is permitted.
If you omit this argument, ALL related records will be returned.

So based on what is highlighted above, it depends on what you want to achieve. Take time to read the link again.

 

[Victor70]

Just cannot wrap my brains around it.

I was following the example for table with company name and order dates in the link provided. Everything seems to be straight forward.

When I omit the third argument I get all the records possible concatenated for each person, not just those related to a particular person.

With this

"name_id = " & [name_id]

I get the concatenation right, but multiple rows per person.

This is the code I am using.

SELECT [Person's Name], ConcatRelated("Record","[DVS - ACTW Persons]", "name_id = " & [name_id]) AS SubFormValues

FROM [DVS - ACTW Persons];

It is bad to be stupid but what can I do?

 

[Victor70]

In my query [DVS - ACTW Persons] I have also rec_id (unique record number) along with name_id (unique person number). Can this cause the results I am getting?

 

[vbaInet]

Why are we going back to the beginning? I mentioned that you should use dbo_nname and wrote the amended SQL in a few posts away but you've gone back to using FROM [DVS - ACTW Persons];?

Does [DVS - ACTW Persons] not display repeated records per person?

 

[Victor70]

Sorry, I must have misinterpreted your statement

Create a query based on dbo_nmain and put in the necessary criteria. Then run the function against this query.

Yes, [DVS - ACTW Persons] produces multiple rows per person (one per record). When I was reading the example provided by you I assumed that the input data to produce the sample table was similar to results of my initial query.

You have set up a one-to-many relationship, and now you want a query to show the records from the table on the ONE side, with the items from the MANY side beside each one. For example if one company has many orders, and you want to list the order dates like this

The last code from your side provided me with all names regardless of whether they had or did not have records associated with them.

I guess there is something very simple that I am missing.