Could you explain why you need to do this?  This is one of those things that generally should NEVER be done in a relational database.  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.   

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

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.  The user has requested that the field on her form list the years next to each other and separated by commas.

Thanks Kathryn.....

jgarnick
jgarnick@aol.com

Thanks for the explanation.  I am hoping that your form is showing only one record at a time, i.e. that it is NOT a continuous form.   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.  This list would then be set as the source of your list box.

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

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("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

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