Running an update query to concatenate a field from a master table to a detail table

I have a table that has a master key and a second table with many records containing the master key and up to 31 dates. I want to update a memo type field on the master table with a concatenation of all the dates in the detail table. For example:
Master table: CallSignID                 1
                      DateMemoField        ""
Detail table: CallSignID                   1
                   Date                          01/01/08
                   CallSign                      1
                   Date                          01/02/08
                  CallSign                       1
                  Date                           01/03/08
After running the update query, I would like the master table to look like this:
                 CallSignID                    1
                 DateMemoField           01/01/08,01/02/08,01/03/08
breauxlgAsked:
Who is Participating?
 
jpipkinsCommented:
Try this:
    Dim rcsDetail As DAO.Recordset, rcsMaster As DAO.Recordset
    Dim fld As DAO.Field, str As String, lngID As Long
    
    Set rcsDetail = CurrentDb.OpenRecordset("Select * from tblDetail order by CallSignID")
    
    
    With rcsDetail
        If Not .BOF And Not .EOF Then
        .MoveFirst
        Do While Not .EOF
            lngID = !CallSignID
            str = str & ![Date] & ", "
            
            .MoveNext
            If lngID <> !CallSignID Then
                If Len(str) > 0 Then
                    str = Left(str, Len(str) - 2)
                End If
                        
                Set rcsMaster = CurrentDb.OpenRecordset("Select * from tblMaster where CallSignID = " & lngID)
                rcsMaster.Edit
                rcsMaster!DateMemo = str
                rcsMaster.Update
                rcsMaster.Close
            End If
        Loop
    End With
    

Open in new window

0
 
Jeffrey CoachmanMIS LiasonCommented:
breauxlg,

hmmmm....

Do I have to tell you that your data does not apear to be "Normalized"

You can't  have 3 fields with the same name in the same table.

JeffCoachman
0
 
breauxlgAuthor Commented:
Those are supposed to represent three records with two field names - CallSignID and Date. I'm not typing so good right now. What I am finally trying to accomplish is for reporting purposes to not have
CallSign 1
      01/01/08
      01/02/08
      01/03/08

But rather
CallSign 1    
      01/02/08,01/02/08,01/03/08.

when I have one master record with three detail records with different dates.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Jeffrey CoachmanMIS LiasonCommented:
breauxlg,

I did this not to long ago.
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Access_Reports/Q_23038691.html
Have a look at it, there is a sample to download.
Try to see if you can adapt it to work in your DB.

Give a good try first, then post back here if you have any issues.

JeffCoachman
0
 
jpipkinsCommented:
If the fields are all on the same row of the db, then try this code.  If not, please provide more information about the design of the tables.  You will need to modify the code to match your table names and field names.
    Dim rcsDetail As DAO.Recordset, rcsMaster As DAO.Recordset
    Dim fld As DAO.Field, str As String
    
    Set rcsDetail = CurrentDb.OpenRecordset("tblDetail")
    
    With rcsDetail
        For Each fld In .Fields()
            If Left(fld.Name, 4) = "Date" Then
                If Len(Nz(fld.Value, "")) > 0 Then
                    str = str & fld.Value & ", "
                End If
            End If
        Next fld
        
        If Len(str) > 0 Then
            str = Left(str, Len(str) - 2)
        End If
                
        Set rcsMaster = CurrentDb.OpenRecordset("Select * from tblMaster where CallSignID = " & !CallSignID)
        rcsMaster.Edit
        rcsMaster!DateMemoField = str
        rcsMaster.Update
        rcsMaster.Close
        
        .MoveNext
    End With

Open in new window

0
 
breauxlgAuthor Commented:
That looks like what I need. Does the vb look different if the detail is coming from a query?
0
 
jpipkinsCommented:
Change this line

Set rcsDetail = CurrentDb.OpenRecordset("tblDetail")

to

Set rcsDetail = CurrentDb.OpenRecordset("qryDetail")

or

Set rcsDetail = CurrentDb.OpenRecordset("{Select statement}")


0
 
breauxlgAuthor Commented:
I made it appear more complicated than I wanted to. The master table has two fields. CallSignID and DateMemo. The detail query has two fields. CallSignID and Date. I have multiple detail records representing multiple dates per CallSignID. I want the DateMemo field to be a concatenation of all of the dates in the detail records for that CallSignID. I guess it doesn't matter if I'm using ADO or DAO as long as it works.
0
 
breauxlgAuthor Commented:
That's what I'm talking about.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.