Combining records into one field

[jgarnick]

I have two tables with a one-to-many relationship related by an id field.&nbsp;&nbsp;I would like to have a field in the &quot;one&quot; table that would summarize a particular field in the &quot;many&quot; table for each id.<br><br>For example:<br><br>Table &quot;one&quot;<br><br>id 10000 <br><br>Table &quot;many&quot;<br><br>id 10000&nbsp;&nbsp;&nbsp;&nbsp;year 1990<br>id 10000&nbsp;&nbsp;&nbsp;&nbsp;year 1993<br>id 10000&nbsp;&nbsp;&nbsp;&nbsp;year 1997<br><br>I would like a field in table &quot;one&quot; to display 1990,1993,1997 in it.&nbsp;&nbsp;I need to be able to do this for the current records in the table AND also have the field in the &quot;one&quot; table updated each time another record is added to table &quot;many&quot;.<br><br>Hope this makes sense to someone out there!&nbsp;&nbsp;Thanks! <p>jgarnick<br><a href=mailto:jgarnick@aol.com>jgarnick@aol.com</a><br><a href= > </a><br>

 

[kathryn]

Could you explain why you need to do this?&nbsp;&nbsp;This is one of those things that generally should NEVER be done in a relational database.&nbsp;&nbsp;The reasons are many; one reason is, as you have already seen, that the two tables will be out of sync as soon as data is added.&nbsp;&nbsp;&nbsp;<br><br>I am 99.9% sure that there is a way to accomplish whatever your end goal is without doing it this way.&nbsp;&nbsp;Give us a bit more detail when you get a chance and we'll see what we can come up with.<br><br> <p>Kathryn<br><a href=mailto: > </a><br><a href= > </a><br>

 

[jgarnick]

Well perhaps I don't need a field in a table that summarizes the data, but I would like a field on a form that will display it in that format. Currently I have a combo box that will display the information, but in a long list.&nbsp;&nbsp;The user has requested that the field on her form list the years next to each other and separated by commas.<br><br>Thanks Kathryn..... <p>jgarnick<br><a href=mailto:jgarnick@aol.com>jgarnick@aol.com</a><br><a href= > </a><br>

 

[kathryn]

Thanks for the explanation.&nbsp;&nbsp;I am hoping that your form is showing only one record at a time, i.e. that it is NOT a continuous form.&nbsp;&nbsp;&nbsp;If it is showing only one record, you could run a function in the Current event of the form which gets the ID of the current record, creates a record set of all years for that ID and then loops throught the recordset and builds a concatenated list.&nbsp;&nbsp;This list would then be set as the source of your list box.<br><br>You might be able to set the source of the list box directly to the funciton....I'll have to think about that.&nbsp;&nbsp;Anyway, does this sound like what you need?&nbsp;&nbsp;If so, give it a try, or let me know if you need more detail. <p>Kathryn<br><a href=mailto: > </a><br><a href= > </a><br>

 

[SYZYGY]

Easy (NOT)
==========================================================
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(&quot;Order Details&quot;, &quot;OrderID&quot;, &quot;Quantity&quot;, _
&quot;Long&quot;, 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 = &quot;Select [&quot; & strFldConcat & &quot;] From [&quot; & strChildTable & &quot;]&quot;
strSQL = strSQL & &quot; Where &quot;

Select Case strIDType
Case &quot;String&quot;:
strSQL = strSQL & &quot;[&quot; & strIDName & &quot;] = '&quot; & varIDvalue & &quot;'&quot;
Case &quot;Long&quot;, &quot;Integer&quot;, &quot;Double&quot;: 'AutoNumber is Type Long
strSQL = strSQL & &quot;[&quot; & strIDName & &quot;] = &quot; & 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) & &quot;;&quot;
.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

 

[elizabeth]

Have you thought of just creating a query to display this to the user? A crosstab query by year might be just the ticket.