×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Combining records into one field

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!

jgarnick
jgarnick@aol.com

RE: Combining records into one field

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


RE: Combining records into one field

(OP)
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

RE: Combining records into one field

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


RE: Combining records into one field

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

RE: Combining records into one field

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close