Concatenate multiple records question

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • Soulspike
    New Member
    • Jan 2008
    • 35

    Concatenate multiple records question

    Hello all,

    I am looking for Ideas as to how to handle some data. I am new to VBA and sql(but love to learn and have not had any luck making queries do what I need them to do here. Can someone point me in a direction that would help.


    The Problem : The data listed below is a single Locomotive unit number and the date and time that it was shopped and released from the shop as well as the Work codes that were completed. What I need is to take the Release time (B) and compair it to Shopped time (C). If they are within 24 hours i need to add the 2 shoppings together and concatenate the work codes as shown in example #2. I need this logic to contininue as long as there are units shopped within 24 hours of the last release date. This could range from 1 - 15 shoppings. Any suggestions or recommended study material to help me figure out how to do this in sql or vba would be greatly appretiated.


    Example #1
    Eqmt# ShoppedDate ReleasedDate Work Codes
    6198 11/11/2007 9:50 PM(A) 11/12/2007 10:57 AM(B) US TF
    6198 11/12/2007 10:58 AM(C) 11/15/2007 3:51 PM(D) PM PI
    6198 11/15/2007 3:52 PM(E) 11/15/2007 4:56 PM(F) PM TF
    6198 11/15/2007 4:57 PM(G) 11/17/2007 3:38 AM(H) PM EC MM



    Example#2
    UP6198 11/11/2007 9:50 PM(A) 11/15/2007 3:51 PM(D) US TF PM PI
  • ADezii
    Recognized Expert Expert
    • Apr 2006
    • 8834

    #2
    Originally posted by Soulspike
    Hello all,

    I am looking for Ideas as to how to handle some data. I am new to VBA and sql(but love to learn and have not had any luck making queries do what I need them to do here. Can someone point me in a direction that would help.


    The Problem : The data listed below is a single Locomotive unit number and the date and time that it was shopped and released from the shop as well as the Work codes that were completed. What I need is to take the Release time (B) and compair it to Shopped time (C). If they are within 24 hours i need to add the 2 shoppings together and concatenate the work codes as shown in example #2. I need this logic to contininue as long as there are units shopped within 24 hours of the last release date. This could range from 1 - 15 shoppings. Any suggestions or recommended study material to help me figure out how to do this in sql or vba would be greatly appretiated.


    Example #1
    Eqmt# ShoppedDate ReleasedDate Work Codes
    6198 11/11/2007 9:50 PM(A) 11/12/2007 10:57 AM(B) US TF
    6198 11/12/2007 10:58 AM(C) 11/15/2007 3:51 PM(D) PM PI
    6198 11/15/2007 3:52 PM(E) 11/15/2007 4:56 PM(F) PM TF
    6198 11/15/2007 4:57 PM(G) 11/17/2007 3:38 AM(H) PM EC MM



    Example#2
    UP6198 11/11/2007 9:50 PM(A) 11/15/2007 3:51 PM(D) US TF PM PI
    It definately can be done, but it would be quite a challenge for someone new to the world of VBA and SQL. I'll just subscribe to this Thread and keep an eye on it for now, if no one comes up with a relatively simple solution in an acceptable time frame, I'll generate the code as soon as I get a chance, and walk you through the logic, OK?

    BTW, 1 piece of information that I'll need is the Table Name that stores this data, what is it?

    Comment

    • jyoung2
      New Member
      • Jan 2008
      • 32

      #3
      Just want to verify, these are in one table and are each name seperate Fields? Is this Appending to a new table.

      So this only matters if there are (A) and a (C) in the same EQMT#

      Comment

      • Soulspike
        New Member
        • Jan 2008
        • 35

        #4
        Thank you Adezii for your generous offer of time and expertise.

        Answer for Adezii: The table that contains all this data is tblGordonData.

        Answer for Jyoung2: Example #1 is 4 records copied from this table exactly. Each record has a different shopping date and time and a different release date and time. There could be as many as 1-15 of each group that would fall into the 24 hour group. So basicly I need the Shop time of the first record and the release time of the X record. vbmenu_register ("postmenu_3038 120", true);

        Comment

        • jyoung2
          New Member
          • Jan 2008
          • 32

          #5
          I have to agree there is no easy way to do it. I would use a nested loop. I would do a make table and open it as a recordset to loop through each record

          To start I would do a Make table qury to find all the (b)
          then I would do another maketable to find the (c) events

          You would have to take the (b) and the (c) off of the date and do a date diff I think you have to do Cdate to convert the string back to a date.

          You will have to nest both recordsets and then do the testing. I have something simmilar if you want me to post it but Quereys just do not let you compare info in diferent records.

          looping through the record sets would let you check through the entire table You have to do the make tables because query's won't open as a record set.
          I'm sorry I don't have better info. If you would like I can post the code I have later tonight or in the morning.

          Comment

          • Soulspike
            New Member
            • Jan 2008
            • 35

            #6
            Please post what you have, the more code I look at the better off I will be. Thank you for everything

            Comment

            • ADezii
              Recognized Expert Expert
              • Apr 2006
              • 8834

              #7
              Originally posted by Soulspike
              [font=Arial]Please post what you have, the more code I look at the better off I will be. Thank you for everything[/font]
              jyoung2 has the correct approach. I'll try to have the code for you by the end of the day or early evening. Stay tuned.

              Comment

              • jyoung2
                New Member
                • Jan 2008
                • 32

                #8
                Code:
                Private Sub butCompleteMerge_Click()
                On Error GoTo Err_butCompleteMerge_Click
                
                'Make Tables
                    DoCmd.SetWarnings False
                    DoCmd.OpenQuery "qmktMerge1"
                    DoCmd.OpenQuery "qmktMerge2"
                    DoCmd.SetWarnings True
                    
                    Dim wrk As Workspace
                    Dim db As Database
                    Dim records1 As Recordset
                    Dim records2 As Recordset
                    Dim RecordChange As Boolean
                    RecordChange = False
                    [B]'Open Record Set [/B] 
                    Set wrk = DBEngine.Workspaces(0)
                    Set db = wrk.Databases(0)
                    Set records1 = db.OpenRecordset("tblTempMerge1", dbOpenDynaset)  'open the recordset
                    Set records2 = db.OpenRecordset("tblTempMerge2", dbOpenDynaset)  'open the recordset
                    
                        
                    If Not records2.BOF Then
                        records2.MoveFirst
                    End If
                    If records2.EOF = True Then   ' No Tournament Entries CustID will be deleted.
                        DoCmd.SetWarnings False
                        DoCmd.OpenQuery "qdelMergeDelete"
                        DoCmd.SetWarnings True
                        Else
                          
                            Do Until records2.EOF
                                If Not records1.BOF Then
                                    records1.MoveFirst
                                End If
                                Do Until records1.EOF
                                    If records1("tournID") = records2("TournID") Then
                                     Me.MergetournId = records1("tournID")
                                      'find cust idstring cut there then add
                                      
                                      Me.OldVisitID = records2("visitID")
                                      Me.mergeVisitID = Me.MergetournId & Me.Merge1 & DLookup("countofVisitID", "qryMergeCountVisitID")
                                      DoCmd.SetWarnings False
                                      DoCmd.OpenQuery "qupdMergeVisitEntry"
                                      DoCmd.SetWarnings True
                                      RecordChange = True
                                    End If
                                    records1.MoveNext
                                Loop
                               If RecordChange = True Then
                               'dontchange
                               RecordChange = False
                               Else
                               'make Change
                                     Me.MergetournId = records2("tournID")
                                     Me.OldVisitID = records2("visitID")
                                     Me.mergeVisitID = Me.MergetournId & Me.Merge1 & 0
                                     DoCmd.SetWarnings False
                                     DoCmd.OpenQuery "qupdMergeVisitEntry"
                                     DoCmd.SetWarnings True
                                    RecordChange = False
                               End If
                               records2.MoveNext
                            Loop
                        DoCmd.SetWarnings False
                        DoCmd.OpenQuery "qupdMergeVisit"
                        DoCmd.OpenQuery "qdelMergeDelete"
                        DoCmd.SetWarnings True
                       End If
                       
                       Me.Merge1 = Null
                       Me.Merge2 = Null
                       Me.CustomerSelection.SetFocus
                       Me.butCompleteMerge.Visible = False
                       Me.Requery
                Exit_butCompleteMerge_Click:
                    Exit Sub
                
                Err_butCompleteMerge_Click:
                    MsgBox Err.Description
                    Resume Exit_butCompleteMerge_Click
                    
                End Sub
                I set this up with a form so I assigned the changes to the form and then run the update query after each group is found to update the new information. If you are using a mofule then you could use
                Code:
                docmd.runsql "Your SQL Code Here"
                or open a third record set to take the information.

                when you set up your date dif you will need to take out the "(A)" at the end i would use below to pull the date and place it in a varible to do the date diff
                Code:
                Cdate(left(ShoppedDate,len(ShoppedDate)-3))
                It would probably not hurt to accept ADezii very generous offer this type of loop takes a good deal of testing to get exactly right. Good luck and most of all have fun.

                Comment

                • ADezii
                  Recognized Expert Expert
                  • Apr 2006
                  • 8834

                  #9
                  Originally posted by Soulspike
                  Hello all,

                  I am looking for Ideas as to how to handle some data. I am new to VBA and sql(but love to learn and have not had any luck making queries do what I need them to do here. Can someone point me in a direction that would help.


                  The Problem : The data listed below is a single Locomotive unit number and the date and time that it was shopped and released from the shop as well as the Work codes that were completed. What I need is to take the Release time (B) and compair it to Shopped time (C). If they are within 24 hours i need to add the 2 shoppings together and concatenate the work codes as shown in example #2. I need this logic to contininue as long as there are units shopped within 24 hours of the last release date. This could range from 1 - 15 shoppings. Any suggestions or recommended study material to help me figure out how to do this in sql or vba would be greatly appretiated.


                  Example #1
                  Eqmt# ShoppedDate ReleasedDate Work Codes
                  6198 11/11/2007 9:50 PM(A) 11/12/2007 10:57 AM(B) US TF
                  6198 11/12/2007 10:58 AM(C) 11/15/2007 3:51 PM(D) PM PI
                  6198 11/15/2007 3:52 PM(E) 11/15/2007 4:56 PM(F) PM TF
                  6198 11/15/2007 4:57 PM(G) 11/17/2007 3:38 AM(H) PM EC MM



                  Example#2
                  UP6198 11/11/2007 9:50 PM(A) 11/15/2007 3:51 PM(D) US TF PM PI
                  I do hope that I interpreted your request correctly, but if I didn't, the code can be easily modified to produce the desired results. I placed comments where I thought they were needed, but if you have any questions at all, please feel free to ask. The code has been thoroughly tested and is operational, I also made the Test Database available to you as an Attachment for you to download, so you can better see what is going on. Good Luck!
                  [CODE=vb]
                  Dim MyDB As DAO.Database, rst_1 As DAO.Recordset, rstClone As DAO.Recordset
                  Dim rstFinalResults As DAO.Recordset

                  ' Before you do anything, cteate a Table named tblFinalResults . This Table will be an
                  ' exact duplicate of tblGordonData, and will, in fact contain the Final Results, This
                  ' can easily be done via: select tblGordonData ==> CTL+C ==> CTRL+V ==> under Table
                  ' Name enter tblFinalResults ==> select Structure Only

                  ' If any prior results exist in tblFinalResults , DELETE them now
                  DoCmd.SetWarnin gs False
                  DoCmd.RunSQL "DELETE * From tblFinalResults ;"
                  DoCmd.SetWarnin gs True

                  Set MyDB = CurrentDb
                  Set rst_1 = MyDB.OpenRecord set("tblGordonD ata", dbOpenSnapshot)
                  Set rstClone = rst_1.Clone 'exact duplicate of rst_1, will be used to check
                  'successive Records againts prior ones
                  Set rstFinalResults = MyDB.OpenRecord set("tblFinalRe sults", dbOpenDynaset)

                  rst_1.MoveFirst
                  rstClone.Move 1 'Current Record is now on Record #2 for the Clone

                  Do
                  'Compare the [ReleasedDate] of each Record to the [ShoppedDate] of the following
                  'Record. If the difference is <= 24 hours, then pair the [ShoppedDate] of the
                  'first Record to the [ReleasedDate] of the second, then concatenate the [Work Codes]
                  'and add all this mess to tblFinalResults . Must check for the same Eqmt#s also.
                  If DateDiff("h", rstClone![ShoppedDate], rst_1![ReleasedDate]) <= 24 And rst_1![Eqmt#] = rstClone![Eqmt#] Then
                  With rstFinalResults
                  .AddNew
                  ![Eqmt#] = rst_1![Eqmt#]
                  ![ShoppedDate] = rst_1![ShoppedDate]
                  ![ReleasedDate] = rstClone![ReleasedDate]
                  ![Work Codes] = rst_1![Work Codes] & " " & rstClone![Work Codes]
                  .Update
                  End With
                  End If
                  rst_1.MoveNext
                  rstClone.MoveNe xt
                  Loop Until rstClone.EOF

                  ' Don't forget to do your clean-up chores, or you'll be punished
                  rst_1.Close: Set rst_1 = Nothing
                  rstClone.Close: Set rstClone = Nothing
                  rstFinalResults .Close: Set rstFinalResults = Nothing

                  'Let's see the final results
                  DoCmd.OpenTable "tblFinalResult s", acViewNormal, acReadOnly[/CODE]

                  Comment

                  • Soulspike
                    New Member
                    • Jan 2008
                    • 35

                    #10
                    Wow, Thank you both very much for all your time on this. I will try to get this up and running this weekend, have been in and out of meetings all week. I can't thank you two enough for your help.

                    Comment

                    • Soulspike
                      New Member
                      • Jan 2008
                      • 35

                      #11
                      oops

                      First off, thanks again for all the help on this problem and I appoligize for some misinformation. Example #2 shown above is slitely incorrect. What should happen if I run the data below Example 3. I should end up with example 4. Because each consecutive record is shopped within 24 hours of the last release date these 4 records should end up as 1 record after run through the code. If there is an easy way to remove duplicate codes that would be awsome, but not necessary. Again I appoligize for messing up my examples above. I think it is very close with the code that ADezii provided. I was able to past that right into my db and it works with the exception of some of the information I messed up. Thanks Adezii again.

                      Example 3 (Data In)
                      Eqmt# ShoppedDate ReleasedDate Work Codes
                      6198 11/11/2007 9:50 PM(A) 11/12/2007 10:57 AM(B) US TF
                      6198 11/12/2007 10:58 AM(C) 11/15/2007 3:51 PM(D) PM PI
                      6198 11/15/2007 3:52 PM(E) 11/15/2007 4:56 PM(F) PM TF
                      6198 11/15/2007 4:57 PM(G) 11/17/2007 3:38 AM(H) PM EC MM


                      Example 4 (Data Out)
                      UP6198 11/11/2007 9:50 PM(A) 111/17/2007 3:38 AM(H) US TF PM PI PM TF PM EC MM

                      Comment

                      • ADezii
                        Recognized Expert Expert
                        • Apr 2006
                        • 8834

                        #12
                        Originally posted by Soulspike
                        First off, thanks again for all the help on this problem and I appoligize for some misinformation. Example #2 shown above is slitely incorrect. What should happen if I run the data below Example 3. I should end up with example 4. Because each consecutive record is shopped within 24 hours of the last release date these 4 records should end up as 1 record after run through the code. If there is an easy way to remove duplicate codes that would be awsome, but not necessary. Again I appoligize for messing up my examples above. I think it is very close with the code that ADezii provided. I was able to past that right into my db and it works with the exception of some of the information I messed up. Thanks Adezii again.

                        Example 3 (Data In)
                        Eqmt# ShoppedDate ReleasedDate Work Codes
                        6198 11/11/2007 9:50 PM(A) 11/12/2007 10:57 AM(B) US TF
                        6198 11/12/2007 10:58 AM(C) 11/15/2007 3:51 PM(D) PM PI
                        6198 11/15/2007 3:52 PM(E) 11/15/2007 4:56 PM(F) PM TF
                        6198 11/15/2007 4:57 PM(G) 11/17/2007 3:38 AM(H) PM EC MM


                        Example 4 (Data Out)
                        UP6198 11/11/2007 9:50 PM(A) 111/17/2007 3:38 AM(H) US TF PM PI PM TF PM EC MM
                        You are quite welcome.

                        Comment

                        • Soulspike
                          New Member
                          • Jan 2008
                          • 35

                          #13
                          Is there a way that I can make this codeing loop untill all records are combined as far as they can go? Or maybe just loop a set number of times to get to the final output In Example #4

                          Comment

                          • ADezii
                            Recognized Expert Expert
                            • Apr 2006
                            • 8834

                            #14
                            Originally posted by Soulspike
                            Is there a way that I can make this codeing loop untill all records are combined as far as they can go? Or maybe just loop a set number of times to get to the final output In Example #4
                            As I understand it, a Loop cannot be set for a fixed number of Iterations because the number of Groupings can vary from 1 to 15, is this correct? I'm a little confused on this scenario, so let me take a step backwards:

                            [CODE=text]
                            Eqmt# ShoppedDate ReleasedDate Work Codes
                            6198 11/11/2007 9:50 PM(A) 11/12/2007 10:57 AM(B) US TF
                            6198 11/12/2007 10:58 AM(C) 11/15/2007 3:51 PM(D) PM PI
                            6198 11/15/2007 3:52 PM(E) 11/15/2007 4:56 PM(F) PM TF
                            6198 11/15/2007 4:57 PM(G) 11/17/2007 3:38 AM(H) PM EC MM[/CODE]
                            'for the same Eqmt# (6198), this would set the Grouping, is this correct?
                            11/12/2007 10:58 AM is Shopped within 24 hrs. of 11/12/2007 10:57 AM
                            11/15/2007 3:52 PM is Shopped within 24 hrs. of 11/15/2007 3:51 PM
                            11/15/2007 4:57 PM is Shopped within 24 hrs. of 11/15/2007 4:56 PM

                            Comment

                            • scubasteve
                              New Member
                              • Jan 2008
                              • 13

                              #15
                              Hi,

                              Apologies if this isn't exactly relevant, but someone may find it useful.

                              This is a function I've got off somewhere on the net and altered to suit (apologies if it was written by anyone reading this, I got a bit overzealous cleaning up my code library and I've lost the credits).

                              I've used it in a query for printing nutritional labels for a bakery - it concatenates all the ingredients in the recipe into one comma-separated string. It could be altered to add min and max dates, etc.

                              Code:
                              Function ConcatenateChildFields(strChildTable As String, _
                                                  strFieldToConcatenate As String, _
                                                  strSeparator As String, _
                                                  strMatchField1 As String, _
                                                  strMatchType1 As String, _
                                                  varMatchValue1 As Variant, _
                                                  Optional strMatchField2 As String, _
                                                  Optional strMatchType2 As String, _
                                                  Optional varMatchValue2 As Variant, _
                                                  Optional strOrderBy As String) _
                                                  As String
                              'Returns a field from the many side of a 1:M relationship
                              'separated by the specified character(s).
                              '
                              'Usage Examples:
                              '   ? ConcatenateChildFields("Order Details", "OrderID", ", ", "Quantity", "Long", 10255)
                              'Where  Order Details = Many side table
                              '       OrderID       = Primary Key of One side table
                              '       ", "          = Separate with comma plus space
                              '       Quantity      = Field name to concatenate
                              '       Long          = DataType of Primary Key of One Side Table
                              '       10255         = Value on which return concatenated Quantity
                              '
                              ' Set a reference to DAO
                              
                                Dim db As DAO.Database
                                Dim rs As DAO.Recordset
                                Dim varConcat As Variant
                                Dim strCriteria As String
                                Dim strSQL As String
                              
                                On Error GoTo Err_ConcatenateChildFields
                                
                                varConcat = Null
                                Set db = CurrentDb
                                
                                strSQL = "SELECT [" & strFieldToConcatenate & "] FROM [" & strChildTable & "]"
                                strSQL = strSQL & " WHERE "
                                
                                Select Case strMatchType1
                                  Case "String":
                                    strSQL = strSQL & "[" & strMatchField1 & "] = '" & varMatchValue1 & "'"
                                  Case "Long", "Integer", "Double":
                                    strSQL = strSQL & "[" & strMatchField1 & "] = " & varMatchValue1
                                  Case "DateTime":
                                    strSQL = strSQL & "[" & strMatchField1 & "] = #" & Format(varMatchValue1, "mm/dd/yyyy") & "#"
                                  Case Else
                                    GoTo Err_ConcatenateChildFields
                                End Select
                                
                                If Not IsMissing(varMatchValue2) Then
                                   strSQL = strSQL & " And "
                                  Select Case strMatchType2
                                    Case "String":
                                      strSQL = strSQL & "[" & strMatchField2 & "] = '" & varMatchValue2 & "'"
                                    Case "Long", "Integer", "Double":
                                      strSQL = strSQL & "[" & strMatchField2 & "] = " & varMatchValue2
                                    Case "DateTime":
                                      strSQL = strSQL & "[" & strMatchField2 & "] = #" & Format(varMatchValue2, "mm/dd/yyyy") & "#"
                                    Case Else
                                      GoTo Err_ConcatenateChildFields
                                  End Select
                                End If
                                
                                If Len(strOrderBy) > 0 Then
                                  strSQL = strSQL & " ORDER BY " & strOrderBy
                                End If
                                
                                Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
                              
                                With rs
                                  If .RecordCount <> 0 Then
                                  'Start concatenating records
                                    Do While Not rs.EOF
                                      If Len(Trim(rs(strFieldToConcatenate))) > 0 Then varConcat = varConcat & rs(strFieldToConcatenate) & strSeparator
                                      .MoveNext
                                    Loop
                                  End If
                                End With
                                    
                              'Trim the trailing separator
                                If Len(varConcat) > 0 Then
                                  ConcatenateChildFields = Left(varConcat, Len(varConcat) - Len(strSeparator))
                                End If
                                    
                              Exit_ConcatenateChildFields:
                                If Not rs Is Nothing Then
                                  rs.Close
                                  Set rs = Nothing
                                End If
                                Set db = Nothing
                                Exit Function
                                
                              Err_ConcatenateChildFields:
                              'Put error message here if you want one.....
                                Resume Exit_ConcatenateChildFields
                                  
                              End Function

                              Comment

                              Working...