Complex looping: set up time continuums

[murre08]

Hi
I need help creating a module in access but I know very little VB. The module will calculate time continuums that will be summed later to get total effort. Here’s the scenario: two or three people record start and stop times when observing a colony. There are many start and stop times in a day, and there are overlapping times between observers as well as time gaps. What I want is the total number of hours the colony is being observed, no matter who or how many people observing.

I have tblEffortInput (Fields: Date, StartTime, StopTime; DataType: Date/Time) and tblEffortOutput (Date, ContinuumNumber, ContinuumStart, and ContinuumStop) to contain the outputs.

Here’s the pseudo-code:

Sort records ascending by Date, StartTime, and StopTime

For each Date
Read the first record
Set the first Continuum to the FIRST Start and Stop times
Next Record
Loop through remaining records until reaching the EndOfFile
Is the new START equal to or earlier than the Continuum STOP?
Yes: Is the new STOP greater than the Continuum STOP?
Yes: Update the Stop of the current Continuum (extends the Continuum to include this observation)
Next Record
No: Next Record
No: Record the current Continuum START and STOP (Ends this Continuum and starts a new one)
Begin a NEW Continuum.
Set current record START and STOP times as the Continuum START and STOP
Next Record
End Loop at EOF

I greatly appreciate your help. Thank you so much!

 

[ajetrumpet]

What I want is the total number of hours the colony is being observed, no matter who or how many people observing.

Well, you will probably need DateDiff(), plus a recordset. Try this code to start with:

on error resume next

dim rs as recordset, prevTime as date, prevDate as date
  dim x as long, TotalTime as long

  set rs = currentdb.openrecordset("SELECT [date], [starttime], [stoptime] _
    FROM tblEffortInput ORDER BY [date], [starttime], [stoptime]",  dbopendynaset)

TotalTime = 0
  x = 0

with rs

  .movelast
  .movefirst

  do until .eof

    if !starttime > prevTime or .absolutepostion = 0 or !date <> prevDate then
      x = datediff("n", !starttime, !endtime)
    else
      x = datediff("n", prevTime, !endtime)
    end if

TotalTime = TotalTime + x
  prevTime = !endtime
    prevDate = !date
      .movenext

  loop

end with

rs.close
  set rs = nothing

You might want to change the field called date. Reserved words are no-no's. You can also modify the function to hold whatever time interval you want. I used minutes for this example.