Loop through each column of an access table

[PokerRebel]

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.

[ADezii]

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.

[PokerRebel]

Sample

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?

[ADezii]

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

Dim MyDB As DAO.database
Dim rst As DAO.Recordset
Dim intFldCtr As Integer
Dim blnFirstTrue As Boolean
Dim blnFirstFalseAfterTrue As Boolean

blnFirstTrue = False
blnFirstFalseAfterTrue = False

Set MyDB = CurrentDb
Set rst = MyDB.OpenRecordset("Table1", dbOpenSnapshot)

Debug.Print "------------------------------------------------"

With rst
  For intFldCtr = 1 To .Fields.Count - 1        'Skip the [TimeStamp] Field
    Do While Not .EOF
      If .Fields(intFldCtr) = True Then
        If Not blnFirstTrue Then        '1st true Value not found, but Field is True
          Debug.Print .Fields(0) & " | " & .Fields(intFldCtr).Name & " | " & .Fields(intFldCtr)
            blnFirstTrue = True         'Found 1st True, Reset
        End If       '1st True was found, now need the 1st False
      Else      'Field is False
        If Not blnFirstFalseAfterTrue And blnFirstTrue Then     '1st False not found, but 1st True was
          If .Fields(intFldCtr) = False Then  '1st True, then 1st False after True
            Debug.Print .Fields(0) & " | " & .Fields(intFldCtr).Name & " | " & .Fields(intFldCtr)
              blnFirstFalseAfterTrue = True
          End If
        End If
      End If
        .MoveNext
    Loop
      blnFirstTrue = False                  'Must Reset both Booleans for next Field
      blnFirstFalseAfterTrue = False
    Debug.Print "------------------------------------------------"
      .MoveFirst
  Next
End With

rst.Close
Set rst = Nothing

OUTPUT based on Sample.txt:

------------------------------------------------
24/02/2010 12:45 | Turbidity_>_20 | TRUE
24/02/2010 12:49 | Turbidity_>_20 | FALSE
------------------------------------------------
------------------------------------------------
24/02/2010 12:33 | Chlorine_SP_High | TRUE
24/02/2010 12:51 | Chlorine_SP_High | FALSE
------------------------------------------------
24/02/2010 12:50 | Flock_LL_Alarm | TRUE
24/02/2010 12:51 | Flock_LL_Alarm | FALSE
------------------------------------------------
24/02/2010 12:42 | Acoustic_LL_Alarm | TRUE
24/02/2010 12:46 | Acoustic_LL_Alarm | FALSE
------------------------------------------------

[PokerRebel]

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.

[PokerRebel]

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.

[ADezii]

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.

Dim MyDB As DAO.database
Dim rst As DAO.Recordset
Dim intFldCtr As Integer
Dim blnFirstTrue As Boolean
Dim blnFirstFalseAfterTrue As Boolean
Dim varEventID As Variant
Dim varColumnName As Variant
Dim varStartDate As Variant
Dim varStartTime As Variant
Dim varEndDate As Variant
Dim varEndTime As Variant

blnFirstTrue = False
blnFirstFalseAfterTrue = False

Set MyDB = CurrentDb
Set rst = MyDB.OpenRecordset("Table1", dbOpenSnapshot)

Debug.Print "-------------------------------------------------------------------------------------"

With rst
  For intFldCtr = 2 To .Fields.Count - 1        'Skip the [EventID], and [TimeStamp] Fields
    Do While Not .EOF
      If .Fields(intFldCtr) = True Then
        If Not blnFirstTrue Then        '1st true Value not found, but Field is True
          varEventID = .Fields(0)
          varColumnName = .Fields(intFldCtr).Name
          varStartDate = Format(.Fields(1), "mm/dd/yyyy")
          varStartTime = Format(.Fields(1), "hh:mm AM/PM")
          'Debug.Print .Fields(0) & " | " & .Fields(intFldCtr).Name & " | " & .Fields(intFldCtr)
            blnFirstTrue = True         'Found 1st True, Reset
        End If       '1st True was found, now need the 1st False
      Else      'Field is False
        If Not blnFirstFalseAfterTrue And blnFirstTrue Then     '1st False not found, but 1st True was
          If .Fields(intFldCtr) = False Then  '1st True, then 1st False after True
            varEndDate = Format(.Fields(1), "mm/dd/yyyy")
            varEndTime = Format(.Fields(1), "hh:mm AM/PM")
            Debug.Print varEventID & " " & varColumnName & " " & " " & varStartDate & " " & _
                        varStartTime & " " & varEndDate & " " & varEndTime & " " & _
                        DateDiff("n", varStartTime, varEndTime) & " minutes"
            'Debug.Print .Fields(0) & " | " & .Fields(intFldCtr).Name & " | " & .Fields(intFldCtr)
              blnFirstFalseAfterTrue = True
          End If
        End If
      End If
        .MoveNext
    Loop
      varEventID = Null
      varColumnName = Null
      varStartDate = Null
      varStartTime = Null
      varEndDate = Null
      varEndTime = Null
        blnFirstTrue = False                  'Must Reset both Booleans for next Field
        blnFirstFalseAfterTrue = False
    Debug.Print "-------------------------------------------------------------------------------------"
      .MoveFirst
  Next
End With

rst.Close
Set rst = Nothing

OUTPUT:

-------------------------------------------------------------------------------------
10 Turbidity_>_20  02/24/2010 12:45 PM 02/24/2010 12:49 PM 4 minutes
-------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------
7 Chlorine_SP_High  02/24/2010 12:33 PM 02/24/2010 12:51 PM 18 minutes
-------------------------------------------------------------------------------------
14 Flock_LL_Alarm  02/24/2010 12:50 PM 02/24/2010 12:51 PM 1 minutes
-------------------------------------------------------------------------------------
9 Acoustic_LL_Alarm  02/24/2010 12:42 PM 02/24/2010 12:46 PM 4 minutes
-------------------------------------------------------------------------------------

[PokerRebel]

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

[PokerRebel]

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.

[ADezii]

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.

[ADezii]

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_Hig h Field?

Chlorine_SP_High
FALSE
FALSE
FALSE
FALSE
FALSE
FALSE
TRUE			Match Pair 1 True
TRUE			Match Pair 2 True
TRUE			Match Pair 3 True
TRUE			Match Pair 4 True
TRUE			Match Pair 5 True
TRUE
TRUE
TRUE
FALSE		   Match Pair 1 False
FALSE		   Match Pair 2 False
FALSE		   Match Pair 3 False
FALSE		   Match Pair 4 False
FALSE		   Match Pair 5 False

[PokerRebel]

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_Hig h, etc. I need to record how long the occurance reocrds "True" for. By working each column individually, ie for Chlorine_SP_Hig h. 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"occurenc e. 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...

[ADezii]

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.

[ADezii]

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.

[PokerRebel]

ADezii, you are a genius. That is absolutely correct. Thank you very much for all the hard work in making this possible.