Concatenate field in many side table

[Autoeng]

Originally posted this in Query forum but it seems that it would be impossible to do via query. After much searching I found this code by Joe McDonnell on Tek-Tips. Problem is I have never used a module to return a data result and have no idea how to use this code. Can someone please help me?

Original post
Combine data
Help! I'm being overrun by data!

In building my latest db I have run into a problem that I don't know how to solve. I have a table that stores part information related to engineering changes. A part can be changed by many engineering changes so a part can appear many times in the table. I want to take one field (Comments) from each record and combine it into one result.

For example

AutoNumberID.........Part Number......................Comments
12...............................100001.........................Implementation 5/03
1053...........................100001.........................Use up old stock first
2563...........................100001.........................Price increase 01/01

The return that I would like is...

100001......Implementation 05/03, Use up old stock first, Price increase 01/01


tblECNParts, PartNumber, Comments are the table and fields. I've read about using Union queries but this seems to be for multiple tables or queries. Can I use it on one table, same field, many times?

Option Compare Database
Option Explicit

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) & ";"
                .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:
    Set rs = Nothing: Set db = Nothing
    Exit Function
Err_fConcatChild:
    Resume Exit_fConcatChild
End Function

 

[Fizzio]

It depends how you want to use it. The code has good commenting that helps break down the process and an example of how to use it.

You may have to cheat a bit as you seem to have the actual part number repeated in the table rather than the ID for that part number, so instead of looking for the ID, look for the part number

correct way is
fConcatChild("tblECNParts", "AutoNumberID", "Comments", "Long", ID for part number 100001)

cheat becomes
fConcatChild("tblECNParts", "PartNumber", "Comments", "Part Number Field Type", 100001) '100001' if Part Number is a string.

Any help?

You can use this in vba or an update query.

vba
strPartSummary = fConcatChild......

Update Query - set the update to as
fConcatChild....

 

[Autoeng]

Thanks Fizzio. Missed you being around.

I need this code to go through the entire table and look at every part that is duplicated and concatenate the "Comments" field. This code (if I'm not incorrect) looks for all instances of a variable (in my case 100001) that you predetermine. I can't predetermine what parts to look for so is there a way to do that? I need to use this in an update query (I suppose) as I am using the results as the recordsource for a Data Access Page. If there is a way to change the code to look at all parts do I only need the line (will be changed) fConcatChild("tblECNParts", "PartNumber", "Comments", "Part Number Field Type", 100001) in the Update To field of the Comments field in the query (rest of code is not required anywhere)?

 

[Fizzio]

Cheers Autoeng, nice to be back.

You are correct that the function only returns the concatenated string for one value - in the original function, it was supposed to be an Autonumber primary key but I'm sure the cheat I suggested will work.

If you want to do this for all your part numbers, it could take a little while so ask yourself how often this will need to be updated?

The update query route will be a bugger I think so you are more likely to get a better result using code and (don't strike me down) a temporary table (arghhh)

How I would accomplish this is to set up a recordset from the part number table.
Grab each part number eg strPartNumber (assuming it is a string)
then
strPartSummary = fConcatChild("tblECNParts", "PartNumber", "Comments", "String", strPartNumber)

open a new recordset with the temp table eg
ID, PartNumber, Comments
create a new record
plug in strPartNumber and strPartSummary into the Partnumber and Comments fields
update
Loop through the parts table.
Cross Fingers

Hope this helps

 

[Pat Hartman]

When you start an new thread on the same topic, you should post a link so that people can see what has already been suggested.

http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=47189

 

[Autoeng]

You're correct Pat. I should have posted a link but didn't as that thread didn't go anywhere. It was mostly just musing over the problem about how it really couldn't be solved via a query. I didn't feel that it would be of any assistance to be able to look at the alternative post. But from now on I will make sure I do post the link.

Thanks Fizzio for the detail you have provided. I plan for this to be updated once per day (first user in triggers event) but at 15,000 records to loop through I think that the code method will be best. As long as I don't rerun the event during the day in the multiuser environ I should be ok with a temp table. I'll give this a shot in the morning and let you know how it goes (cross fingers).