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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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.
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
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

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

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

Set rcsDetail = CurrentDb.OpenRecordset("tblDetail")

to

Set rcsDetail = CurrentDb.OpenRecordset("qryDetail")

or

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


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.
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
breauxlgAuthor Commented:
That's what I'm talking about.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.