Half hour intervals

[qh8519a]

I am trying to come up with a way to identify the half hour impact from
several exceptions across multiple days.

The fields that are used would be a start moment ( 9/3/2008 3:45:00 PM),
stop moment (9/3/2008 4:30:00 PM), and total minutes (45). I have hundreds
of exceptions with the above data and I need to identify the half hourly
impact. I was initially thinking of using the countif function across all 48
daily intervals, but I was getting hung up with exceptions that crossed
midnight.

When I have one exception with a start time of 7:15 and an end time of 8:20,
I would like to see the following impacts:

7:00 - .5
7:30 - 1
8:00 - .66

I have another exception with a start time 7:30 and an end time of 9:15, I
would like to see the following impacts:

7:30 – 1
8:00 – 1
8:30 – 1
9:00 - .5

Then when I roll them all up, I would see the following:

7:00 - .5
7:30 – 2
8:00 – 1.66
8:30 – 1
9:00 - .5


Thanks again for all of your help!
Drew

 

[Stefan Hoffmann]

hi Drew,

When I have one exception with a start time of 7:15 and an end time of 8:20,
I would like to see the following impacts:

7:00 - .5
7:30 - 1
8:00 - .66

I think you need a function like:

Public Function CalculateImpact(AInterval As Date, _
AStart As Date, _
AEnd As Date) As Double
' The parameters are TimeValues.
' AInterval marks your interval start.
' [AStart;AEnd] is the actual interval to test.

' Check boundaries.
' Calculate impact.

CalculateImpact = 0 ' Return real impact.

End Function

when you want to use queries only.

I have hundreds
of exceptions with the above data and I need to identify the half hourly
impact.

Depending on your needs I think the better solution is to loop over your
exceptions in a recordset and store the calculated impacts in a second
table...

mfG
--> stefan <--

 

[qh8519a]

Thanks for your help Stefan!

I am having problems putting together the timevalue function. You mentioned
looping over exceptions in a recordset and storing the calculated impacts in
a second table...how would I go about doin this?

Thanks again,
Drew

hi Drew,

When I have one exception with a start time of 7:15 and an end time of 8:20,
I would like to see the following impacts:

7:00 - .5
7:30 - 1
8:00 - .66

I think you need a function like:

Public Function CalculateImpact(AInterval As Date, _
AStart As Date, _
AEnd As Date) As Double
' The parameters are TimeValues.
' AInterval marks your interval start.
' [AStart;AEnd] is the actual interval to test.

' Check boundaries.
' Calculate impact.

CalculateImpact = 0 ' Return real impact.

End Function

when you want to use queries only.

I have hundreds
of exceptions with the above data and I need to identify the half hourly
impact.

Depending on your needs I think the better solution is to loop over your
exceptions in a recordset and store the calculated impacts in a second
table...

mfG
--> stefan <--

 

[Stefan Hoffmann]

hi Drew,

I am having problems putting together the timevalue function. You mentioned
looping over exceptions in a recordset and storing the calculated impacts in
a second table...how would I go about doin this?

Something like this:

Public Sub Test()

Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("yourExceptions")
If Not rs.Bof And Not rs.Eof Then
Do While Not rs.Eof
CalculateAndStoreImpact rs![ID], rs![Start], rs![End]
rs.MoveNext
Loop
End If
rs.Close
Set rs = Nothing

End Sub

Public Sub CalculateAndStoreImpact(AId As Long, _
AStart As Date, AEnd As Date)

Dim Count As Integer
Dim Impact As Double
Dim SQL As String


' determine impact for first interval
SQL = "INSERT INTO table (id, intNo, impact)" & _
"VALUES (" & AId & ", " & Count & ", " & Str(Impact) & ")"
CurrenDb.Execute SQL, dbFailOnError

For Count = GetIntervalNo(AStart) + 1 To GetIntervalNo(AEnd) - 1
SQL = "INSERT INTO table (id, intNo, impact)" & _
"VALUES (" & AId & ", " & Count & ", 1)"
CurrenDb.Execute SQL, dbFailOnError
Next Count

If GetIntervalNo(AStart) <> GetIntervalNo(AEnd) Then
' determine impact for last interval
SQL = "INSERT INTO table (id, intNo, impact)" & _
"VALUES (" & AId & ", " & Count & ", " & Str(Impact) & ")"
CurrenDb.Execute SQL, dbFailOnError
End If

End Sub

Public Function GetIntervalNo(ATime As Date) As Integer
' There are 48 intervals (2 per hour, 0-47) per day:

GetIntervalNo = Int(TimeValue(ATime) * 48)

End Function

mfG
--> stefan <--

 

[qh8519a]

Thanks for the detailed assistance!

I have tried tweeking the code to insert my variables, but I keep running
into errors. I have a feeling I am replacing the wrong variables. I
replaced the following:

yourexceptions -> my table name
Astart -> my exception start time
Aend -> my exception endtime

The error usually occurs around 'CurrenDb.Execute SQL, dbFailOnError'.

Again, thank you very much for all of your help.
Drew

hi Drew,

I am having problems putting together the timevalue function. You mentioned
looping over exceptions in a recordset and storing the calculated impacts in
a second table...how would I go about doin this?

Something like this:

Public Sub Test()

Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("yourExceptions")
If Not rs.Bof And Not rs.Eof Then
Do While Not rs.Eof
CalculateAndStoreImpact rs![ID], rs![Start], rs![End]
rs.MoveNext
Loop
End If
rs.Close
Set rs = Nothing

End Sub

Public Sub CalculateAndStoreImpact(AId As Long, _
AStart As Date, AEnd As Date)

Dim Count As Integer
Dim Impact As Double
Dim SQL As String


' determine impact for first interval
SQL = "INSERT INTO table (id, intNo, impact)" & _
"VALUES (" & AId & ", " & Count & ", " & Str(Impact) & ")"
CurrenDb.Execute SQL, dbFailOnError

For Count = GetIntervalNo(AStart) + 1 To GetIntervalNo(AEnd) - 1
SQL = "INSERT INTO table (id, intNo, impact)" & _
"VALUES (" & AId & ", " & Count & ", 1)"
CurrenDb.Execute SQL, dbFailOnError
Next Count

If GetIntervalNo(AStart) <> GetIntervalNo(AEnd) Then
' determine impact for last interval
SQL = "INSERT INTO table (id, intNo, impact)" & _
"VALUES (" & AId & ", " & Count & ", " & Str(Impact) & ")"
CurrenDb.Execute SQL, dbFailOnError
End If

End Sub

Public Function GetIntervalNo(ATime As Date) As Integer
' There are 48 intervals (2 per hour, 0-47) per day:

GetIntervalNo = Int(TimeValue(ATime) * 48)

End Function

mfG
--> stefan <--

 

[Stefan Hoffmann]

hi Drew,

The error usually occurs around 'CurrenDb.Execute SQL, dbFailOnError'.

Yeah, obviously I left something blank to do...


mfG
--> stefan <--