Concatenate multiple records question

**ADezii** · Jan 22 '08, 12:07 AM

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?

**jyoung2** · Jan 22 '08, 12:38 AM

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#

**Soulspike** · Jan 22 '08, 02:16 AM

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);

**jyoung2** · Jan 22 '08, 03:29 AM

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.

**Soulspike** · Jan 22 '08, 06:06 AM

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

**ADezii** · Jan 22 '08, 01:33 PM

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.

**jyoung2** · Jan 22 '08, 04:09 PM

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.

**ADezii** · Jan 22 '08, 06:01 PM

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]

**Soulspike** · Jan 25 '08, 02:53 AM

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.

**Soulspike** · Jan 27 '08, 08:11 PM

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

**ADezii** · Jan 28 '08, 01:25 AM

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.

**Soulspike** · Jan 28 '08, 08:44 PM

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

**ADezii** · Jan 29 '08, 12:58 AM

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

**scubasteve** · Jan 29 '08, 10:44 AM

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

Concatenate multiple records question

Concatenate multiple records question

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment