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

Loop through each column of an access table

P: 10
I am new to MS Access and was wondering how do I code the following using vba and looping each column in the table.

I have a table with 6 columns with about 500 records in the table. Col1 contains a date and time format. Col's 2 - 6 contain random "True" and "False" values in each column. I need to read col2, find the 1st "True" row (call it start time and date), lookup the date and time and add the start date and start time to a new table. it must then find the next "False" value in the same column (call it end time and date) and copy those values to the new table.

I must be able to do this for each of the columns. The new table must have the data added for each of the column names. ID, Col_Name, Start_Date, Start_Time, End_Date, End_Time.

I hope this makes sense. Please could someone assist me.
Mar 3 '10 #1
Share this Question
Share on Google+
20 Replies


ADezii
Expert 5K+
P: 8,638
Kindly provide some concrete Data, and the desired results that you are looking for. I'm a little hazy on the content of the question.
Mar 3 '10 #2

P: 10
Thanks for the speedy response.

I have a data logger that records events. Its creates a CSV file that i want to import into access to manipulate the data and write reports. When an event occurs it allocates "TRUE", with a date stamp (see txt attached for sample). Each column records a different event.

I need to know when the event records "True" (ie the date and time) and when the next "False" event occurs. (ie its date and time). In that column. So now i have a start date and time and an end date and time. One column could have 5 start and end dates and times, while another column could only have 2.

I thought it would be easier to create a new table and populate it with the extracted information and when be able to write a query to work out the time difference between the "True" and its next "False" event.

Does that help?
Attached Files
File Type: txt Sample.txt (990 Bytes, 1130 views)
Mar 3 '10 #3

ADezii
Expert 5K+
P: 8,638
The following code will process every Record in your Table (let's call it Table1) on a Column by Column basis recording the 1st occurrence of True in a Column, then the 1st occurrence of a False after the initial True is found. It should at least point you in the right direction. There is probably a less complex SQL solution that the SQL Gang will come up with, but therein lies my weakness (SQL).
Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As DAO.database
  2. Dim rst As DAO.Recordset
  3. Dim intFldCtr As Integer
  4. Dim blnFirstTrue As Boolean
  5. Dim blnFirstFalseAfterTrue As Boolean
  6.  
  7. blnFirstTrue = False
  8. blnFirstFalseAfterTrue = False
  9.  
  10. Set MyDB = CurrentDb
  11. Set rst = MyDB.OpenRecordset("Table1", dbOpenSnapshot)
  12.  
  13. Debug.Print "------------------------------------------------"
  14.  
  15. With rst
  16.   For intFldCtr = 1 To .Fields.Count - 1        'Skip the [TimeStamp] Field
  17.     Do While Not .EOF
  18.       If .Fields(intFldCtr) = True Then
  19.         If Not blnFirstTrue Then        '1st true Value not found, but Field is True
  20.           Debug.Print .Fields(0) & " | " & .Fields(intFldCtr).Name & " | " & .Fields(intFldCtr)
  21.             blnFirstTrue = True         'Found 1st True, Reset
  22.         End If       '1st True was found, now need the 1st False
  23.       Else      'Field is False
  24.         If Not blnFirstFalseAfterTrue And blnFirstTrue Then     '1st False not found, but 1st True was
  25.           If .Fields(intFldCtr) = False Then  '1st True, then 1st False after True
  26.             Debug.Print .Fields(0) & " | " & .Fields(intFldCtr).Name & " | " & .Fields(intFldCtr)
  27.               blnFirstFalseAfterTrue = True
  28.           End If
  29.         End If
  30.       End If
  31.         .MoveNext
  32.     Loop
  33.       blnFirstTrue = False                  'Must Reset both Booleans for next Field
  34.       blnFirstFalseAfterTrue = False
  35.     Debug.Print "------------------------------------------------"
  36.       .MoveFirst
  37.   Next
  38. End With
  39.  
  40. rst.Close
  41. Set rst = Nothing
OUTPUT based on Sample.txt:
Expand|Select|Wrap|Line Numbers
  1. ------------------------------------------------
  2. 24/02/2010 12:45 | Turbidity_>_20 | TRUE
  3. 24/02/2010 12:49 | Turbidity_>_20 | FALSE
  4. ------------------------------------------------
  5. ------------------------------------------------
  6. 24/02/2010 12:33 | Chlorine_SP_High | TRUE
  7. 24/02/2010 12:51 | Chlorine_SP_High | FALSE
  8. ------------------------------------------------
  9. 24/02/2010 12:50 | Flock_LL_Alarm | TRUE
  10. 24/02/2010 12:51 | Flock_LL_Alarm | FALSE
  11. ------------------------------------------------
  12. 24/02/2010 12:42 | Acoustic_LL_Alarm | TRUE
  13. 24/02/2010 12:46 | Acoustic_LL_Alarm | FALSE
  14. ------------------------------------------------
Mar 3 '10 #4

P: 10
How would you insert your output data in line 2 and line 3 into a table called "Data_rs", with these fields. EventID, Column_Name, Start_Date, Start_Time, End_Date, End_Time.

See actual data log file attached. I need to report on how long the event actually occured for during the day The event in each column in the sample could occur more than once and I need to total for the day.eg. the query works out the difference bewteen the start and end times and totals up the differences per day.

i am sorry to mess you around. Your code seems to work, but i need the output to go into a table. Thank you again for your assistance.
Attached Files
File Type: zip Digital.zip (24.2 KB, 280 views)
Mar 3 '10 #5

P: 10
I also have a probelm when the events totals per day, per column exceeds 24hours. My result when calculating the difference between the start date / time and the end date / time.

How do i over come that if i need tihe report to show 2 days 5 hours and 15 mins, or some format similar to that.
Mar 3 '10 #6

ADezii
Expert 5K+
P: 8,638
Hopefully, the following code will point you in the right direction. I think what you are looking for is in Lines 38, 39, and 40.
Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As DAO.database
  2. Dim rst As DAO.Recordset
  3. Dim intFldCtr As Integer
  4. Dim blnFirstTrue As Boolean
  5. Dim blnFirstFalseAfterTrue As Boolean
  6. Dim varEventID As Variant
  7. Dim varColumnName As Variant
  8. Dim varStartDate As Variant
  9. Dim varStartTime As Variant
  10. Dim varEndDate As Variant
  11. Dim varEndTime As Variant
  12.  
  13. blnFirstTrue = False
  14. blnFirstFalseAfterTrue = False
  15.  
  16. Set MyDB = CurrentDb
  17. Set rst = MyDB.OpenRecordset("Table1", dbOpenSnapshot)
  18.  
  19. Debug.Print "-------------------------------------------------------------------------------------"
  20.  
  21. With rst
  22.   For intFldCtr = 2 To .Fields.Count - 1        'Skip the [EventID], and [TimeStamp] Fields
  23.     Do While Not .EOF
  24.       If .Fields(intFldCtr) = True Then
  25.         If Not blnFirstTrue Then        '1st true Value not found, but Field is True
  26.           varEventID = .Fields(0)
  27.           varColumnName = .Fields(intFldCtr).Name
  28.           varStartDate = Format(.Fields(1), "mm/dd/yyyy")
  29.           varStartTime = Format(.Fields(1), "hh:mm AM/PM")
  30.           'Debug.Print .Fields(0) & " | " & .Fields(intFldCtr).Name & " | " & .Fields(intFldCtr)
  31.             blnFirstTrue = True         'Found 1st True, Reset
  32.         End If       '1st True was found, now need the 1st False
  33.       Else      'Field is False
  34.         If Not blnFirstFalseAfterTrue And blnFirstTrue Then     '1st False not found, but 1st True was
  35.           If .Fields(intFldCtr) = False Then  '1st True, then 1st False after True
  36.             varEndDate = Format(.Fields(1), "mm/dd/yyyy")
  37.             varEndTime = Format(.Fields(1), "hh:mm AM/PM")
  38.             Debug.Print varEventID & " " & varColumnName & " " & " " & varStartDate & " " & _
  39.                         varStartTime & " " & varEndDate & " " & varEndTime & " " & _
  40.                         DateDiff("n", varStartTime, varEndTime) & " minutes"
  41.             'Debug.Print .Fields(0) & " | " & .Fields(intFldCtr).Name & " | " & .Fields(intFldCtr)
  42.               blnFirstFalseAfterTrue = True
  43.           End If
  44.         End If
  45.       End If
  46.         .MoveNext
  47.     Loop
  48.       varEventID = Null
  49.       varColumnName = Null
  50.       varStartDate = Null
  51.       varStartTime = Null
  52.       varEndDate = Null
  53.       varEndTime = Null
  54.         blnFirstTrue = False                  'Must Reset both Booleans for next Field
  55.         blnFirstFalseAfterTrue = False
  56.     Debug.Print "-------------------------------------------------------------------------------------"
  57.       .MoveFirst
  58.   Next
  59. End With
  60.  
  61. rst.Close
  62. Set rst = Nothing
OUTPUT:
Expand|Select|Wrap|Line Numbers
  1. -------------------------------------------------------------------------------------
  2. 10 Turbidity_>_20  02/24/2010 12:45 PM 02/24/2010 12:49 PM 4 minutes
  3. -------------------------------------------------------------------------------------
  4. -------------------------------------------------------------------------------------
  5. 7 Chlorine_SP_High  02/24/2010 12:33 PM 02/24/2010 12:51 PM 18 minutes
  6. -------------------------------------------------------------------------------------
  7. 14 Flock_LL_Alarm  02/24/2010 12:50 PM 02/24/2010 12:51 PM 1 minutes
  8. -------------------------------------------------------------------------------------
  9. 9 Acoustic_LL_Alarm  02/24/2010 12:42 PM 02/24/2010 12:46 PM 4 minutes
  10. -------------------------------------------------------------------------------------
Mar 3 '10 #7

P: 10
The output file only loops the column once and creates the following results:
"2830 Turbidity_>_20 03/01/2010 08:39 AM 03/01/2010 08:39 AM 0 minutes", which is correct, but there are more ocurrences in that column. And those are the other records that are important. Not only just the 1st

eg it also occurs on
3342 | Turbidity_>_20 | 02/03/2010 00:01:26 | True
3343 | Turbidity_>_20 | 02/03/2010 00:01:30 | False

and i need that info as well
Mar 3 '10 #8

P: 10
Please can you help. The code works fine... i mod'd it a bit to write to a new table and also caluculate the time difference in a different format.

All i need the code to do is loop the entire columns and find ALL the "true" values and the next "false" values that occur in the column. At the moment it is only finding the first occurance.
Mar 5 '10 #9

ADezii
Expert 5K+
P: 8,638
Didn't forget you, I have been extremely busy. I'll see if I can change the logic to accommodate 'ALL' the True then the next False Values using the DB and Data that you gave me.
Mar 5 '10 #10

ADezii
Expert 5K+
P: 8,638
If my assumptions are correct, the logic now must be considerably more complex. Before I attempt to arrive at a solution, I must know that these assumptions are in fact correct. Based on the Data in Sample.txt that you provided in Post #3, are the following True/next False pairings correct fdor the Chlorine_SP_High Field?
Expand|Select|Wrap|Line Numbers
  1. Chlorine_SP_High
  2. FALSE
  3. FALSE
  4. FALSE
  5. FALSE
  6. FALSE
  7. FALSE
  8. TRUE            Match Pair 1 True
  9. TRUE            Match Pair 2 True
  10. TRUE            Match Pair 3 True
  11. TRUE            Match Pair 4 True
  12. TRUE            Match Pair 5 True
  13. TRUE
  14. TRUE
  15. TRUE
  16. FALSE           Match Pair 1 False
  17. FALSE           Match Pair 2 False
  18. FALSE           Match Pair 3 False
  19. FALSE           Match Pair 4 False
  20. FALSE           Match Pair 5 False
  21.  
Mar 5 '10 #11

P: 10
No! that is not right. Take a look at the actual data file in post #5. What happens is that the logger records occurances for the Turbidity_>_20, Chlorine_SP_High, etc. I need to record how long the occurance reocrds "True" for. By working each column individually, ie for Chlorine_SP_High. To work out the time difference, i find the first "True" occurrance in the column and subtract the next time a "False" occurrance occurs

Here is a manual example using the zipped .csv file in post #5.

Scanning colum Turbidity_>_20

01/03/2010 08:39 TRUE (Start)
01/03/2010 08:39 FALSE (End)

02/03/2010 00:01 TRUE (Start)
02/03/2010 00:01 FALSE (End)

02/03/2010 00:02 TRUE (Start)
02/03/2010 00:11 FALSE (End)

02/03/2010 00:13 TRUE (Start)
02/03/2010 00:13 FALSE (End)

02/03/2010 00:14 TRUE (Start)
02/03/2010 00:14 FALSE (End)

02/03/2010 00:15 TRUE (Start)
02/03/2010 00:17 FALSE (End)

02/03/2010 00:18 TRUE (Start)
02/03/2010 00:18 FALSE (End)

02/03/2010 00:20 TRUE (Start)
02/03/2010 03:03 FALSE (End)

02/03/2010 03:18 TRUE (Start)
02/03/2010 03:21 FALSE (End)

02/03/2010 03:27 TRUE (Start)
02/03/2010 03:36 FALSE (End)

02/03/2010 04:33 TRUE (Start)
02/03/2010 07:46 FALSE (End)

... until the end of the column.

Each column must be looped. Your vba code did it correctly, but only found the first "True" and the first next "False"occurence. Then it looped to the next column. it should have continued to scan the rest of the column and find the next "True" occurences and its next "False" occurence, so that i can subtract the start time (TRUE) from the end time (Flase).

I hope this makes more sense? Once again thanks for all the help...
Mar 6 '10 #12

ADezii
Expert 5K+
P: 8,638
I do beleive that I fully comprehend the problem at hand and I'll see if I can come up with a viable solution. This is one of those problems, at least for me, that was very difficult to understand from the oposite end of a Web Page. I'll see if I can come up with something for you.
Mar 6 '10 #13

ADezii
Expert 5K+
P: 8,638
I 'may' have good news for you, PokerRebel. Simply Download the Demo Database that I have been working on. It is Attached to this Post.

P.S. - I will not Post the final code until I absolutely know that it is working correctly. I'm also aware that there is a problem with Negative Time display (when spanning days), but that is not the Major Issue now, and can easily be fixed once I know that the overall Logic is correct.
Attached Files
File Type: zip Loop Columns.zip (157.1 KB, 371 views)
Mar 6 '10 #14

P: 10
ADezii, you are a genius. That is absolutely correct. Thank you very much for all the hard work in making this possible.
Mar 6 '10 #15

ADezii
Expert 5K+
P: 8,638
Not a problem, tomorrow or the next day, I'll see if I can get the Time Differentials to display correctly. How exactly would you like the Time Difference to appear in tblResults? Right now it is in Minutes in the Field [Diff (mins)].
Mar 6 '10 #16

100+
P: 157
Expand|Select|Wrap|Line Numbers
  1. Sub Gammel_Populer_Liste(SQL As String, Skjema As String, Liste As String)
  2. Dim r As New ADODB.Recordset
  3. Dim Coll As New Collection
  4. Dim Tmpstr As String
  5. '--------------------------
  6. 'pne recordset
  7. '--------------------------
  8.  
  9.  
  10. Set r = CurrentDb.OpenRecordset(SQL)
  11.  
  12. 'r.Open SQL, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
  13.  
  14. '--------------------------
  15. 'Finn overskrifter
  16. '--------------------------
  17. For x = 1 To r.Fields.Count
  18.     Coll.ADD (r.Fields(x - 1).Name)
  19. Next x
  20.  
  21. '--------------------------
  22. 'Lag en string med overskriftene
  23. '--------------------------
  24. For x = 1 To Coll.Count
  25.     MsgBox Coll(x)
  26.     If Not Tmpstr = "" Then
  27.         Tmpstr = Tmpstr & ";" & Coll(x)
  28.     Else
  29.         Tmpstr = Coll(x)
  30.     End If
  31. Next x
  32.  
  33. '--------------------------
  34. 'Endre kildetype p listen
  35. '--------------------------
  36. Forms(Skjema).Controls(Liste).RowSourceType = "Verdiliste"
  37.  
  38. '--------------------------
  39. 'Sett inn overskriftene i listen
  40. '--------------------------
  41. Forms(Skjema).Controls(Liste).RowSource = ""
  42. Forms(Skjema).Controls(Liste).AddItem Tmpstr
  43.  
  44. '--------------------------
  45. 'Hent og sett inn verdiene
  46. '--------------------------
  47.  
  48. '--------------------------
  49. 'G gjennom alle postene
  50. '--------------------------
  51. Do Until r.EOF
  52.  
  53.     '--------------------------
  54.     'Tm Coll
  55.     '--------------------------
  56.     For x = 1 To Coll.Count
  57.         Coll.Remove (1)
  58.     Next x
  59.  
  60.     '--------------------------
  61.     'Hent verdier
  62.     '--------------------------
  63.     For x = 1 To r.Fields.Count
  64.         Coll.ADD (r.Fields(x - 1).Value)
  65.     Next x
  66.  
  67.     '--------------------------
  68.     'Lag string
  69.     '--------------------------
  70.     Tmpstr = ""
  71.     For x = 1 To Coll.Count
  72.         If Not Tmpstr = "" Then
  73.             Tmpstr = Tmpstr & ";" & Coll(x)
  74.         Else
  75.             Tmpstr = Coll(x)
  76.         End If
  77.     Next x
  78.  
  79.     '--------------------------
  80.     'Sett inn post i listen
  81.     '--------------------------
  82.     Forms(Skjema).Controls(Liste).AddItem Tmpstr
  83.  
  84.     r.MoveNext
  85. Loop
  86. r.Close
  87.  
  88.  
  89. Set rs = Nothing
  90. Set Coll = Nothing
  91.  
  92. End Sub
Her is an code example from on of my employees, maybe this also is a solution?
Mar 6 '10 #17

P: 10
Hey ADezii. It does not matter what format you give the time difference to me in. I can always change it myself.
Mar 7 '10 #18

ADezii
Expert 5K+
P: 8,638
I have made the following changes/enhancements to the Project and will Upload the Revised Database sometime this afternoon:
  1. Eliminated, for the most part, the display of Negative Values for the Time Differential by revising the manner in which it is calculated.
  2. In addition to the Standard Differential Display in Minutes as a Whole number, added a more intuitive Display as an additional Column, such as:
    1 Day(s) - 4 Hour(s) - 37 Minute(s), No Differential, Undetermined, etc.
  3. Cleaned up the code a little.
  4. Created a simple Query that will display erroneous Results such as when an End Date is earlier than the Start Date (Negative Values).

P.S. - I'm not sure as to whether you are aware of it or not, but there is a problem with the Data as it exists in Digital.csv. The Date Range progresses accordingly in Digital.csv: 2/24/2010 thru 2/28/2010, 1/3/2010, then 2/3/2010. If the last entry ([TimeStamp]) for 2/28/2010 happens to be True and at least the first entry for 1/3/2010 is True, herein lies the problem. The Logic, rightfully so, interprets this as a legitimate True Range, then finds the Next False Value. When this happens, the Differential is calculated, but unfortunately the End Date is earlier than the Start Date, hence erroneous results (Negative Values). I'll probably return an Error! Value for these Records, then you do whatever you like with them. There are also a couple of Records where the End Date is in fact greater than the Start Date, but the difference is much greater than normally anticipated. Just wasn't sure if you were aware of this or not. Will return with the Revised DB later on.
Mar 7 '10 #19

P: 10
I don't know how much to thank you, you have been a tremendous help.

I am aware that there would be lost data as such because the digital.csv file gets brought back dauily and the table gets updates. So! there might be data or occurances that have not completed as such. If you understand what i am saying. I will only run the report for those days that i have completed data for and also, they must understand that the data recieved could be incomplete as some time differences actually run over 24 hours.

THANK YOU! for what u have done...
Mar 7 '10 #20

ADezii
Expert 5K+
P: 8,638
You are quite welcome. Ironically, the problem was not so much in finding the Solution, as it was understanding the Problem! (LOL). Download the Attachment to View what I consider to be the 'Final Version' of this Project. It can probably be improved somewhat, but right now It Ain't Broke so Don't Fix It! (LOL). If you have time, get back to me on this Attachment to see what you think and to make sure that it is fully operational. Take care.
Attached Files
File Type: zip Loop Columns_Ver_2.zip (168.3 KB, 521 views)
Mar 7 '10 #21

Post your reply

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