Half Hour Interval Impacts

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

Ex. If an exception was from 10:00-11:00, then the exception would impact
intervals 10:00-10:30 and 10:30-11:00.

Thanks for any thoughts and guidance.

Drew

 

[John W. Vinson]

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.

Ex. If an exception was from 10:00-11:00, then the exception would impact
intervals 10:00-10:30 and 10:30-11:00.

Thanks for any thoughts and guidance.

Drew

This can be handled with the aid of a general-purpose utility table. Mine's
called Num, with one Long Integer field N, manually filled with values from 0
through 10000 (or any reasonable number).

You can create a Query:

SELECT DateAdd("n", 30*N, [StartMoment]) AS Timeslot, <other fields you
haven't specified>
FROM yourtable, NUM
WHERE N <= DateDiff("n", [StartMoment], [EndMoment]) \ 30;

A Totals query grouping by Timeslot should get you on the right track.

 

[qh8519a]

Thanks! This got me going in the right direction, that is for sure!!

I used a make table query for the results and then used the count function
to see the impact. The challenge I running into now is the number of
intervals. I only want to see half hour intervals, 10:00, 10:30, 11:00 etc.
Many of the exceptions I am importing have start time of 10:07 or 10:42.
This created several more intervals than I was looking for. Now, for the
6:30 interval, I also have 6:40, 6:45, and 6:57. Is there a way that I would
be group these intervals together without double counting the impact?

Thanks
Drew

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.

Ex. If an exception was from 10:00-11:00, then the exception would impact
intervals 10:00-10:30 and 10:30-11:00.

Thanks for any thoughts and guidance.

Drew

This can be handled with the aid of a general-purpose utility table. Mine's
called Num, with one Long Integer field N, manually filled with values from 0
through 10000 (or any reasonable number).

You can create a Query:

SELECT DateAdd("n", 30*N, [StartMoment]) AS Timeslot, <other fields you
haven't specified>
FROM yourtable, NUM
WHERE N <= DateDiff("n", [StartMoment], [EndMoment]) \ 30;

A Totals query grouping by Timeslot should get you on the right track.

 

[John W. Vinson]

I used a make table query for the results and then used the count function
to see the impact. The challenge I running into now is the number of
intervals. I only want to see half hour intervals, 10:00, 10:30, 11:00 etc.
Many of the exceptions I am importing have start time of 10:07 or 10:42.
This created several more intervals than I was looking for. Now, for the
6:30 interval, I also have 6:40, 6:45, and 6:57. Is there a way that I would
be group these intervals together without double counting the impact?

Yes, using the auxiliary table I suggested. You could use a criterion to
select a time between the half-hour intervals in the table.

Alternatively, you can actually "round down" the time in your table, which may
be simpler. Create a query based on your table and include a calculated field:

Timeblock: CDate(Fix(CDbl([datefield]) * 48)/48.)

This tricky expression takes advantage of how Access stores dates-as a count
of days and fractions of a day (times) since midnight, December 30, 1899. The
expression converts the date/time field to a Double, multiplies by 48 to get
it into the number of half-hour intervals rather than the number of days,
truncates that to an integer using Fix(), and converts back to a date/time.

You could then Group By this Timeblock expression.

 

[qh8519a]

Thank you for your conitnued help!!

When I try to enter 'Timeblock: CDate(Fix(CDbl([datefield]) * 48)/48.)' I
get a message that the expression is too complex.

Thanks
Drew

I used a make table query for the results and then used the count function
to see the impact. The challenge I running into now is the number of
intervals. I only want to see half hour intervals, 10:00, 10:30, 11:00 etc.
Many of the exceptions I am importing have start time of 10:07 or 10:42.
This created several more intervals than I was looking for. Now, for the
6:30 interval, I also have 6:40, 6:45, and 6:57. Is there a way that I would
be group these intervals together without double counting the impact?

Yes, using the auxiliary table I suggested. You could use a criterion to
select a time between the half-hour intervals in the table.

Alternatively, you can actually "round down" the time in your table, which may
be simpler. Create a query based on your table and include a calculated field:

Timeblock: CDate(Fix(CDbl([datefield]) * 48)/48.)

This tricky expression takes advantage of how Access stores dates-as a count
of days and fractions of a day (times) since midnight, December 30, 1899. The
expression converts the date/time field to a Double, multiplies by 48 to get
it into the number of half-hour intervals rather than the number of days,
truncates that to an integer using Fix(), and converts back to a date/time.

You could then Group By this Timeblock expression.

 

[John W. Vinson]

Thank you for your conitnued help!!

When I try to enter 'Timeblock: CDate(Fix(CDbl([datefield]) * 48)/48.)' I
get a message that the expression is too complex.

Where are you trying to enter it? I just put it in one of my databases and it
works fine. Did you change datefield to the name of your date/time field? is
that an actual table field or a calculated one?

Perhaps you could post the complete SQL of your query.

 

[qh8519a]

Thank you so much!! I didn't change the datefield name.

I appreciate all of your continued help!

Drew

Thank you for your conitnued help!!

When I try to enter 'Timeblock: CDate(Fix(CDbl([datefield]) * 48)/48.)' I
get a message that the expression is too complex.

Where are you trying to enter it? I just put it in one of my databases and it
works fine. Did you change datefield to the name of your date/time field? is
that an actual table field or a calculated one?

Perhaps you could post the complete SQL of your query.

 

[qh8519a]

Hi John!

I have made allot of progress with this database so far. You stated that I
could use a criterion to select a time between the half-hour intervals in the
table, and I think this may be what I need to do. It is very possible I am
not explaining myself too good here, so let me see if I can try again with an
example.

When I have an 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 gotten very close with all of your help, but when using the rounding
option, I am not getting the detail that I am looking for.

Thanks again for all of your help!
Drew

I used a make table query for the results and then used the count function
to see the impact. The challenge I running into now is the number of
intervals. I only want to see half hour intervals, 10:00, 10:30, 11:00 etc.
Many of the exceptions I am importing have start time of 10:07 or 10:42.
This created several more intervals than I was looking for. Now, for the
6:30 interval, I also have 6:40, 6:45, and 6:57. Is there a way that I would
be group these intervals together without double counting the impact?

Yes, using the auxiliary table I suggested. You could use a criterion to
select a time between the half-hour intervals in the table.

Alternatively, you can actually "round down" the time in your table, which may
be simpler. Create a query based on your table and include a calculated field:

Timeblock: CDate(Fix(CDbl([datefield]) * 48)/48.)

This tricky expression takes advantage of how Access stores dates-as a count
of days and fractions of a day (times) since midnight, December 30, 1899. The
expression converts the date/time field to a Double, multiplies by 48 to get
it into the number of half-hour intervals rather than the number of days,
truncates that to an integer using Fix(), and converts back to a date/time.

You could then Group By this Timeblock expression.