Calculating "ON" Time with 2 independent Variables.

  • Thread starter Thread starter SRaper
  • Start date Start date
S

SRaper

Hi,

I have been creating databases for years, however I have a type of problem
that I have never faced before, and cant see the trees through the forest.

I want to calculate the amount of time Device B is "On", only when a second
independent variable Device A is "On" as well. I now how to flag when these
conditions are true, but I cant see how to do the calculation with the way
the data is formatted, and don't know how to reformat it to get it done.

Here is an example of the data set I have

1 01/01/2004 04:35:00 Device A On
2 01/01/2004 05:25:00 Device A Off
3 01/01/2004 05:35:00 Device B On
4 01/01/2004 07:25:00 Device B OFF
5 01/01/2004 09:45:00 Device A On
6 01/01/2004 10:15:00 Device B On
7 01/01/2004 10:25:00 Device A Off
8 01/01/2004 11:15:00 Device B Off

The calculated value for this data set would be 10 minutes, as the only time
the above conditions were true was when
in line 5 Device A came on, the in line 6 Device B came on, however it
became false in line 7 when Device A shut off.

My issue is I cant understand how to get the date/time values in a format
which will allow me to do the calculations.

I would appreciate any help or direction you may be able to give.

Sincerely,

Stephen Raper.
 
Hi Stephen,

Nice challenge this one! The logic reminds me of discrete time simulation I
used to work on ages ago...

Assume table Device_Status:
TimeStamp (date)
DeviceID (text)
Status (Boolean)

The following piece of code does it!

Sub sum_device_on_time()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim stata As Boolean
Dim statb As Boolean
Dim timon As Date
Dim tstp As Date
Dim counton As Boolean

stata = False
statb = False
counton = False
timon = 0
strSQL = " SELECT TimeStamp, DeviceID, Status from Device_Status ORDER by
TimeStamp, DeviceID"
Set db = CurrentDb()
Set rst = db.OpenRecordset(strSQL)
rst.MoveFirst
tstp = rst.Fields(0)
Do Until rst.EOF
Select Case rst.Fields(1)
Case "Device A"
stata = rst.Fields(2)
Case "Device B"
statb = rst.Fields(2)
End Select
If stata * statb = 1 Then
tstp = rst.Fields(0)
counton = True
Else
If stata + stab = 0 And counton = True Then
timon = timon + rst.Fields(0) - tstp
counton = False
End If
End If
rst.MoveNext
Loop
rst.Close
Debug.Print Format$(timon, "hh:nn")
End Sub

HTH,
Nikos
 
Back
Top