Combining records into one field
Combining records into one field
(OP)
I have two tables with a one-to-many relationship related by an id field. I would like to have a field in the "one" table that would summarize a particular field in the "many" table for each id.
For example:
Table "one"
id 10000
Table "many"
id 10000 year 1990
id 10000 year 1993
id 10000 year 1997
I would like a field in table "one" to display 1990,1993,1997 in it. I need to be able to do this for the current records in the table AND also have the field in the "one" table updated each time another record is added to table "many".
Hope this makes sense to someone out there! Thanks!
For example:
Table "one"
id 10000
Table "many"
id 10000 year 1990
id 10000 year 1993
id 10000 year 1997
I would like a field in table "one" to display 1990,1993,1997 in it. I need to be able to do this for the current records in the table AND also have the field in the "one" table updated each time another record is added to table "many".
Hope this makes sense to someone out there! Thanks!
jgarnick
jgarnick@aol.com
RE: Combining records into one field
I am 99.9% sure that there is a way to accomplish whatever your end goal is without doing it this way. Give us a bit more detail when you get a chance and we'll see what we can come up with.
Kathryn
RE: Combining records into one field
Thanks Kathryn.....
jgarnick
jgarnick@aol.com
RE: Combining records into one field
You might be able to set the source of the list box directly to the funciton....I'll have to think about that. Anyway, does this sound like what you need? If so, give it a try, or let me know if you need more detail.
Kathryn
RE: Combining records into one field
==========================================================
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
===========================================================
Enjoy - Joe McDonnell
RE: Combining records into one field