By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,963 Members | 1,749 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,963 IT Pros & Developers. It's quick & easy.

Concatenate multiple records question

P: 35
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
Jan 21 '08 #1
Share this Question
Share on Google+
22 Replies


ADezii
Expert 5K+
P: 8,668
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?
Jan 22 '08 #2

P: 32
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#
Jan 22 '08 #3

P: 35
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_3038120", true);
Jan 22 '08 #4

P: 32
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.
Jan 22 '08 #5

P: 35
Please post what you have, the more code I look at the better off I will be. Thank you for everything
Jan 22 '08 #6

ADezii
Expert 5K+
P: 8,668
[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.
Jan 22 '08 #7

P: 32
Expand|Select|Wrap|Line Numbers
  1. Private Sub butCompleteMerge_Click()
  2. On Error GoTo Err_butCompleteMerge_Click
  3.  
  4. 'Make Tables
  5.     DoCmd.SetWarnings False
  6.     DoCmd.OpenQuery "qmktMerge1"
  7.     DoCmd.OpenQuery "qmktMerge2"
  8.     DoCmd.SetWarnings True
  9.  
  10.     Dim wrk As Workspace
  11.     Dim db As Database
  12.     Dim records1 As Recordset
  13.     Dim records2 As Recordset
  14.     Dim RecordChange As Boolean
  15.     RecordChange = False
  16.     'Open Record Set  
  17.     Set wrk = DBEngine.Workspaces(0)
  18.     Set db = wrk.Databases(0)
  19.     Set records1 = db.OpenRecordset("tblTempMerge1", dbOpenDynaset)  'open the recordset
  20.     Set records2 = db.OpenRecordset("tblTempMerge2", dbOpenDynaset)  'open the recordset
  21.  
  22.  
  23.     If Not records2.BOF Then
  24.         records2.MoveFirst
  25.     End If
  26.     If records2.EOF = True Then   ' No Tournament Entries CustID will be deleted.
  27.         DoCmd.SetWarnings False
  28.         DoCmd.OpenQuery "qdelMergeDelete"
  29.         DoCmd.SetWarnings True
  30.         Else
  31.  
  32.             Do Until records2.EOF
  33.                 If Not records1.BOF Then
  34.                     records1.MoveFirst
  35.                 End If
  36.                 Do Until records1.EOF
  37.                     If records1("tournID") = records2("TournID") Then
  38.                      Me.MergetournId = records1("tournID")
  39.                       'find cust idstring cut there then add
  40.  
  41.                       Me.OldVisitID = records2("visitID")
  42.                       Me.mergeVisitID = Me.MergetournId & Me.Merge1 & DLookup("countofVisitID", "qryMergeCountVisitID")
  43.                       DoCmd.SetWarnings False
  44.                       DoCmd.OpenQuery "qupdMergeVisitEntry"
  45.                       DoCmd.SetWarnings True
  46.                       RecordChange = True
  47.                     End If
  48.                     records1.MoveNext
  49.                 Loop
  50.                If RecordChange = True Then
  51.                'dontchange
  52.                RecordChange = False
  53.                Else
  54.                'make Change
  55.                      Me.MergetournId = records2("tournID")
  56.                      Me.OldVisitID = records2("visitID")
  57.                      Me.mergeVisitID = Me.MergetournId & Me.Merge1 & 0
  58.                      DoCmd.SetWarnings False
  59.                      DoCmd.OpenQuery "qupdMergeVisitEntry"
  60.                      DoCmd.SetWarnings True
  61.                     RecordChange = False
  62.                End If
  63.                records2.MoveNext
  64.             Loop
  65.         DoCmd.SetWarnings False
  66.         DoCmd.OpenQuery "qupdMergeVisit"
  67.         DoCmd.OpenQuery "qdelMergeDelete"
  68.         DoCmd.SetWarnings True
  69.        End If
  70.  
  71.        Me.Merge1 = Null
  72.        Me.Merge2 = Null
  73.        Me.CustomerSelection.SetFocus
  74.        Me.butCompleteMerge.Visible = False
  75.        Me.Requery
  76. Exit_butCompleteMerge_Click:
  77.     Exit Sub
  78.  
  79. Err_butCompleteMerge_Click:
  80.     MsgBox Err.Description
  81.     Resume Exit_butCompleteMerge_Click
  82.  
  83. 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
Expand|Select|Wrap|Line Numbers
  1. 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
Expand|Select|Wrap|Line Numbers
  1. 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.
Jan 22 '08 #8

ADezii
Expert 5K+
P: 8,668
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!
Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As DAO.Database, rst_1 As DAO.Recordset, rstClone As DAO.Recordset
  2. Dim rstFinalResults As DAO.Recordset
  3.  
  4. ' Before you do anything, cteate a Table named tblFinalResults. This Table will be an
  5. ' exact duplicate of tblGordonData, and will, in fact contain the Final Results, This
  6. ' can easily be done via: select tblGordonData ==> CTL+C ==> CTRL+V ==> under Table
  7. ' Name enter tblFinalResults ==> select Structure Only
  8.  
  9. ' If any prior results exist in tblFinalResults, DELETE them now
  10. DoCmd.SetWarnings False
  11.   DoCmd.RunSQL "DELETE * From tblFinalResults;"
  12. DoCmd.SetWarnings True
  13.  
  14. Set MyDB = CurrentDb
  15. Set rst_1 = MyDB.OpenRecordset("tblGordonData", dbOpenSnapshot)
  16. Set rstClone = rst_1.Clone      'exact duplicate of rst_1, will be used to check
  17.                                 'successive Records againts prior ones
  18. Set rstFinalResults = MyDB.OpenRecordset("tblFinalResults", dbOpenDynaset)
  19.  
  20. rst_1.MoveFirst
  21. rstClone.Move 1     'Current Record is now on Record #2 for the Clone
  22.  
  23. Do
  24.   'Compare the [ReleasedDate] of each Record to the [ShoppedDate] of the following
  25.   'Record. If the difference is <= 24 hours, then pair the [ShoppedDate] of the
  26.   'first Record to the [ReleasedDate] of the second, then concatenate the [Work Codes]
  27.   'and add all this mess to tblFinalResults. Must check for the same Eqmt#s also.
  28.   If DateDiff("h", rstClone![ShoppedDate], rst_1![ReleasedDate]) <= 24 And rst_1![Eqmt#] = rstClone![Eqmt#] Then
  29.     With rstFinalResults
  30.       .AddNew
  31.         ![Eqmt#] = rst_1![Eqmt#]
  32.         ![ShoppedDate] = rst_1![ShoppedDate]
  33.         ![ReleasedDate] = rstClone![ReleasedDate]
  34.         ![Work Codes] = rst_1![Work Codes] & " " & rstClone![Work Codes]
  35.       .Update
  36.     End With
  37.   End If
  38.     rst_1.MoveNext
  39.     rstClone.MoveNext
  40. Loop Until rstClone.EOF
  41.  
  42. ' Don't forget to do your clean-up chores, or you'll be punished
  43. rst_1.Close: Set rst_1 = Nothing
  44. rstClone.Close: Set rstClone = Nothing
  45. rstFinalResults.Close: Set rstFinalResults = Nothing
  46.  
  47. 'Let's see the final results
  48. DoCmd.OpenTable "tblFinalResults", acViewNormal, acReadOnly
Jan 22 '08 #9

P: 35
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.
Jan 25 '08 #10

P: 35
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
Jan 27 '08 #11

ADezii
Expert 5K+
P: 8,668
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.
Jan 28 '08 #12

P: 35
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
Jan 28 '08 #13

ADezii
Expert 5K+
P: 8,668
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:

Expand|Select|Wrap|Line Numbers
  1. Eqmt# ShoppedDate             ReleasedDate            Work Codes
  2. 6198  11/11/2007 9:50 PM(A)   11/12/2007 10:57 AM(B)  US TF 
  3. 6198  11/12/2007 10:58 AM(C)  11/15/2007 3:51 PM(D)   PM PI 
  4. 6198  11/15/2007 3:52 PM(E)   11/15/2007 4:56 PM(F)   PM TF 
  5. 6198  11/15/2007 4:57 PM(G)   11/17/2007 3:38 AM(H)   PM EC MM
'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
Jan 29 '08 #14

P: 13
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.

Expand|Select|Wrap|Line Numbers
  1.  
  2. Function ConcatenateChildFields(strChildTable As String, _
  3.                     strFieldToConcatenate As String, _
  4.                     strSeparator As String, _
  5.                     strMatchField1 As String, _
  6.                     strMatchType1 As String, _
  7.                     varMatchValue1 As Variant, _
  8.                     Optional strMatchField2 As String, _
  9.                     Optional strMatchType2 As String, _
  10.                     Optional varMatchValue2 As Variant, _
  11.                     Optional strOrderBy As String) _
  12.                     As String
  13. 'Returns a field from the many side of a 1:M relationship
  14. 'separated by the specified character(s).
  15. '
  16. 'Usage Examples:
  17. '   ? ConcatenateChildFields("Order Details", "OrderID", ", ", "Quantity", "Long", 10255)
  18. 'Where  Order Details = Many side table
  19. '       OrderID       = Primary Key of One side table
  20. '       ", "          = Separate with comma plus space
  21. '       Quantity      = Field name to concatenate
  22. '       Long          = DataType of Primary Key of One Side Table
  23. '       10255         = Value on which return concatenated Quantity
  24. '
  25. ' Set a reference to DAO
  26.  
  27.   Dim db As DAO.Database
  28.   Dim rs As DAO.Recordset
  29.   Dim varConcat As Variant
  30.   Dim strCriteria As String
  31.   Dim strSQL As String
  32.  
  33.   On Error GoTo Err_ConcatenateChildFields
  34.  
  35.   varConcat = Null
  36.   Set db = CurrentDb
  37.  
  38.   strSQL = "SELECT [" & strFieldToConcatenate & "] FROM [" & strChildTable & "]"
  39.   strSQL = strSQL & " WHERE "
  40.  
  41.   Select Case strMatchType1
  42.     Case "String":
  43.       strSQL = strSQL & "[" & strMatchField1 & "] = '" & varMatchValue1 & "'"
  44.     Case "Long", "Integer", "Double":
  45.       strSQL = strSQL & "[" & strMatchField1 & "] = " & varMatchValue1
  46.     Case "DateTime":
  47.       strSQL = strSQL & "[" & strMatchField1 & "] = #" & Format(varMatchValue1, "mm/dd/yyyy") & "#"
  48.     Case Else
  49.       GoTo Err_ConcatenateChildFields
  50.   End Select
  51.  
  52.   If Not IsMissing(varMatchValue2) Then
  53.      strSQL = strSQL & " And "
  54.     Select Case strMatchType2
  55.       Case "String":
  56.         strSQL = strSQL & "[" & strMatchField2 & "] = '" & varMatchValue2 & "'"
  57.       Case "Long", "Integer", "Double":
  58.         strSQL = strSQL & "[" & strMatchField2 & "] = " & varMatchValue2
  59.       Case "DateTime":
  60.         strSQL = strSQL & "[" & strMatchField2 & "] = #" & Format(varMatchValue2, "mm/dd/yyyy") & "#"
  61.       Case Else
  62.         GoTo Err_ConcatenateChildFields
  63.     End Select
  64.   End If
  65.  
  66.   If Len(strOrderBy) > 0 Then
  67.     strSQL = strSQL & " ORDER BY " & strOrderBy
  68.   End If
  69.  
  70.   Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
  71.  
  72.   With rs
  73.     If .RecordCount <> 0 Then
  74.     'Start concatenating records
  75.       Do While Not rs.EOF
  76.         If Len(Trim(rs(strFieldToConcatenate))) > 0 Then varConcat = varConcat & rs(strFieldToConcatenate) & strSeparator
  77.         .MoveNext
  78.       Loop
  79.     End If
  80.   End With
  81.  
  82. 'Trim the trailing separator
  83.   If Len(varConcat) > 0 Then
  84.     ConcatenateChildFields = Left(varConcat, Len(varConcat) - Len(strSeparator))
  85.   End If
  86.  
  87. Exit_ConcatenateChildFields:
  88.   If Not rs Is Nothing Then
  89.     rs.Close
  90.     Set rs = Nothing
  91.   End If
  92.   Set db = Nothing
  93.   Exit Function
  94.  
  95. Err_ConcatenateChildFields:
  96. 'Put error message here if you want one.....
  97.   Resume Exit_ConcatenateChildFields
  98.  
  99. End Function
  100.  
  101.  
Jan 29 '08 #15

P: 35
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:

Expand|Select|Wrap|Line Numbers
  1. Eqmt# ShoppedDate ReleasedDate Work Codes
  2. 6198 11/11/2007 9:50 PM(A) 11/12/2007 10:57 AM(B) US TF 
  3. 6198 11/12/2007 10:58 AM(C) 11/15/2007 3:51 PM(D) PM PI 
  4. 6198 11/15/2007 3:52 PM(E) 11/15/2007 4:56 PM(F) PM TF 
  5. 6198 11/15/2007 4:57 PM(G) 11/17/2007 3:38 AM(H) PM EC MM
'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
Yes you are correct, so those shoppings in the end would need to be one record. Because Each unit is shopped and released within 24 hours of the other I need to combine the records into 1 useing the ShoppedDate from the first record and the releasedDate from the 4th record. Then concatinate all of the work codes. I dont know if this makes a differance but there will be some records that may not have another within 24 hours so those would just remain as single records. The problem is that I am trying to determine cycle time for one of our shops. The customer transfers units from different locations in the shop and so the data shows multiple shoppings when in reality it was actually one. So to get an accurate Cycle time I have to combine them all. I hope this helps to clarify, thanks again for the help.
Jan 30 '08 #16

ADezii
Expert 5K+
P: 8,668
Yes you are correct, so those shoppings in the end would need to be one record. Because Each unit is shopped and released within 24 hours of the other I need to combine the records into 1 useing the ShoppedDate from the first record and the releasedDate from the 4th record. Then concatinate all of the work codes. I dont know if this makes a differance but there will be some records that may not have another within 24 hours so those would just remain as single records. The problem is that I am trying to determine cycle time for one of our shops. The customer transfers units from different locations in the shop and so the data shows multiple shoppings when in reality it was actually one. So to get an accurate Cycle time I have to combine them all. I hope this helps to clarify, thanks again for the help.
This is more complicated than initially envisioned: each [ShoppedDate] must be compared with the successive [ReleasedDate], as long as the Interval remains < 24 hrs., and as long as the Eqmt# does not change, continue this process. At the breakpoint, when duration > 24 hrs. or Eqmt# changes, capture the initial [ShoppedDate] from the first Record, the [ReleasedDate] from the last Record, and concetenate all Work Codes in this Interval. Before I go crazy trying to implement this logic, is my thinking exact? The original code can be used for a Template, we just need a Method to store all the interim values, probably an Array. Kindly clarify the above before I regroup (LOL).
Jan 30 '08 #17

P: 35
Sounds like you understand. Here is and example.

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


You take shopping #1 [ReleasedDate] and compare to Shopping #2 [ShoppedDate] If <=24 hours then replace shopping #1 [ReleaseDate] with Shopping #2 [ReleaseDate] and concantinate work codes. continue this logic untill no other shoppings are within 24 hours. So with the code run on the 4 records above you would end up with one record that =

6198, Shopping#1.[ShoppedDate], Shopping#4.[ReleasedDate] and All above work codes.

I hope this helps to solidify the problem. And thanks for the continued help. If there is additional data or information you need let me know.

Thanks
Jan 31 '08 #18

ADezii
Expert 5K+
P: 8,668
Sounds like you understand. Here is and example.

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


You take shopping #1 [ReleasedDate] and compare to Shopping #2 [ShoppedDate] If <=24 hours then replace shopping #1 [ReleaseDate] with Shopping #2 [ReleaseDate] and concantinate work codes. continue this logic untill no other shoppings are within 24 hours. So with the code run on the 4 records above you would end up with one record that =

6198, Shopping#1.[ShoppedDate], Shopping#4.[ReleasedDate] and All above work codes.

I hope this helps to solidify the problem. And thanks for the continued help. If there is additional data or information you need let me know.

Thanks
I'm kind of busy right now, but as soon as I get a chance, I'll see what, if anything, I can come up with.
Jan 31 '08 #19

P: 35
No worries, no Hurry, I appretate everything you have already done. Thank you very much
Jan 31 '08 #20

ADezii
Expert 5K+
P: 8,668
No worries, no Hurry, I appretate everything you have already done. Thank you very much
This is proving to be a very difficult nut to crack, I'll show you what I have so far, then let me know what you think.
Feb 2 '08 #21

P: 35
very cool, looks like it works, I will test it on the table tomorrow and let you know how it goes. But looks like it does exactly what I needed. I can't thank you enough.
Feb 3 '08 #22

ADezii
Expert 5K+
P: 8,668
very cool, looks like it works, I will test it on the table tomorrow and let you know how it goes. But looks like it does exactly what I needed. I can't thank you enough.
Don't want to sound negative, but it has only been tested on a single Eqmt# having Records which all fall into the < 24 hours span requirement consecutively.
Feb 3 '08 #23

Post your reply

Sign in to post your reply or Sign up for a free account.