Duane: Calendar report by hours over 2 days

[Michael Noblet]

I have a calendar report that I have designed based on the
crew rotation schedule example in Duane Hookom's calendar
database. I am creating a utilization report of operating
rooms and I am grouping by date then by tim and then by or
room. The problem I run into is that I have procedures
that start on one day and run into the next.

surgery starts at 23:30 and goes until 01:30. The VB
script with the formating function that draws the calendar
line does not like that. How can I get around the
overlapping day problem?

 

[Duane Hookom]

You would have to make sure the lngDuration value + lngStart don't add up to
greater than your day. Do you think you need to break a record into two
records if it crosses a day end? You would need to do this in the code or
query. I would certainly not change the way you add or edit records.

 

[Michael Noblet]

I am thinking that I would need to break the record up.

Case starts on 06/01/2004 at 23:30 and ends on 6/2/2004 at
01:30.

currently the data is populated with a start date, start
time and end time. I was thinking that I would truncate
the origanal record to show 23:30 to 23:59 and then have a
new record with a start date of 6/2/2004 fro 00:00 to
01:30.

I would prefer to do this in the query, but would be just
as comfortable doing it with code. Any thoughts on a
procedure to make this happen or am I heading down the
wrong path.

 

[Duane Hookom]

You are absolutely on the right track. I just figured out a no code
solution.
1) create a table tblDayNums with a single numeric field [DayNum] and two
records with values 0 and 1
2) Create a query with this SQL. You will need to substitute your own table
and field names. I used:
tblSchedule
==============
SchDate
SchStartTime
SchEndTime
SchProcedure
SchRoom
SELECT [SchDate]+[DayNum] AS ScheduleDate, tblSchedule.SchStartTime,
tblSchedule.SchEndTime, IIf([SchEndTime]<[SchStartTime] And
[DayNum]=1,#12:00 AM#,[SchStartTime]) AS CalcStartTime,
IIf([SchEndTime]<[SchStartTime] And [DayNum]=0,#11:59 PM#,[SchEndTime]) AS
CalEndTime, tblSchedule.SchRoom, tblSchedule.SchProcedure, tblDayNums.DayNum
FROM tblDayNums, tblSchedule
WHERE (((tblDayNums.DayNum)<=Abs([SchEndTime]<[SchStartTime])));

ScheduleDate, CalcStartTime and CalcEndTime should be used in the report.

 

[Michael Noblet]

Look slike that should work

-----Original Message-----
You are absolutely on the right track. I just figured out a no code
solution.
1) create a table tblDayNums with a single numeric field [DayNum] and two
records with values 0 and 1
2) Create a query with this SQL. You will need to substitute your own table
and field names. I used:
tblSchedule
==============
SchDate
SchStartTime
SchEndTime
SchProcedure
SchRoom
SELECT [SchDate]+[DayNum] AS ScheduleDate, tblSchedule.SchStartTime,
tblSchedule.SchEndTime, IIf([SchEndTime]<[SchStartTime] And
[DayNum]=1,#12:00 AM#,[SchStartTime]) AS CalcStartTime,
IIf([SchEndTime]<[SchStartTime] And [DayNum]=0,#11:59 PM#, [SchEndTime]) AS
CalEndTime, tblSchedule.SchRoom,

tblSchedule.SchProcedure, tblDayNums.DayNum

FROM tblDayNums, tblSchedule
WHERE (((tblDayNums.DayNum)<=Abs([SchEndTime]< [SchStartTime])));

ScheduleDate, CalcStartTime and CalcEndTime should be used in the report.

--
Duane Hookom
MS Access MVP
--

I am thinking that I would need to break the record up.

Case starts on 06/01/2004 at 23:30 and ends on 6/2/2004 at
01:30.

currently the data is populated with a start date, start
time and end time. I was thinking that I would truncate
the origanal record to show 23:30 to 23:59 and then have a
new record with a start date of 6/2/2004 fro 00:00 to
01:30.

I would prefer to do this in the query, but would be just
as comfortable doing it with code. Any thoughts on a
procedure to make this happen or am I heading down the
wrong path.
on
the by
or

.

 

[Michael Noblet]

Duane,

That worked great. Would this be the best way to split
times into blocks? If I am trying to to calulate the
usage for a block of 7am to 3 pm and then 3pm to 5pm. If
the procedure runs from 2pm to 4 pm, I need to split it
between the 2 groups.

I would assume the same approach would work, but I could
use a bit of help on the calculations in the SQL statement.

Mike

-----Original Message-----
Look slike that should work

-----Original Message-----
You are absolutely on the right track. I just figured

out
a no code

solution.
1) create a table tblDayNums with a single numeric field [DayNum] and two
records with values 0 and 1
2) Create a query with this SQL. You will need to substitute your own table
and field names. I used:
tblSchedule
==============
SchDate
SchStartTime
SchEndTime
SchProcedure
SchRoom
SELECT [SchDate]+[DayNum] AS ScheduleDate, tblSchedule.SchStartTime,
tblSchedule.SchEndTime, IIf([SchEndTime]<[SchStartTime] And
[DayNum]=1,#12:00 AM#,[SchStartTime]) AS CalcStartTime,
IIf([SchEndTime]<[SchStartTime] And [DayNum]=0,#11:59

PM#,
[SchEndTime]) AS

CalEndTime, tblSchedule.SchRoom,

tblSchedule.SchProcedure, tblDayNums.DayNum

FROM tblDayNums, tblSchedule
WHERE (((tblDayNums.DayNum)<=Abs([SchEndTime]< [SchStartTime])));

ScheduleDate, CalcStartTime and CalcEndTime should be used in the report.

--
Duane Hookom
MS Access MVP
--

I am thinking that I would need to break the record up.

Case starts on 06/01/2004 at 23:30 and ends on

6/2/2004

at

then

by


.

.

 

[Duane Hookom]

Mike,
You would need to use similar expressions. I don't have the time to work
through the exact syntax but it should work after some trial and error. I
would be particularly careful to not hard-code your times into the
expressions.

--
Duane Hookom
MS Access MVP
--

Duane,

That worked great. Would this be the best way to split
times into blocks? If I am trying to to calulate the
usage for a block of 7am to 3 pm and then 3pm to 5pm. If
the procedure runs from 2pm to 4 pm, I need to split it
between the 2 groups.

I would assume the same approach would work, but I could
use a bit of help on the calculations in the SQL statement.

Mike

-----Original Message-----
Look slike that should work

-----Original Message-----
You are absolutely on the right track. I just figured

out
a no code

solution.
1) create a table tblDayNums with a single numeric field [DayNum] and two
records with values 0 and 1
2) Create a query with this SQL. You will need to substitute your own table
and field names. I used:
tblSchedule
==============
SchDate
SchStartTime
SchEndTime
SchProcedure
SchRoom
SELECT [SchDate]+[DayNum] AS ScheduleDate, tblSchedule.SchStartTime,
tblSchedule.SchEndTime, IIf([SchEndTime]<[SchStartTime] And
[DayNum]=1,#12:00 AM#,[SchStartTime]) AS CalcStartTime,
IIf([SchEndTime]<[SchStartTime] And [DayNum]=0,#11:59

PM#,
[SchEndTime]) AS

CalEndTime, tblSchedule.SchRoom,

tblSchedule.SchProcedure, tblDayNums.DayNum

FROM tblDayNums, tblSchedule
WHERE (((tblDayNums.DayNum)<=Abs([SchEndTime]< [SchStartTime])));

ScheduleDate, CalcStartTime and CalcEndTime should be used in the report.

--
Duane Hookom
MS Access MVP
--

I am thinking that I would need to break the record up.

Case starts on 06/01/2004 at 23:30 and ends on

6/2/2004

at
01:30.

currently the data is populated with a start date, start
time and end time. I was thinking that I would truncate
the origanal record to show 23:30 to 23:59 and then have a
new record with a start date of 6/2/2004 fro 00:00 to
01:30.

I would prefer to do this in the query, but would be just
as comfortable doing it with code. Any thoughts on a
procedure to make this happen or am I heading down the
wrong path.

-----Original Message-----
You would have to make sure the lngDuration value +
lngStart don't add up to
greater than your day. Do you think you need to break a
record into two
records if it crosses a day end? You would need to do
this in the code or
query. I would certainly not change the way you add or
edit records.

--
Duane Hookom
MS Access MVP


"Michael Noblet" <[email protected]>
wrote in message
I have a calendar report that I have designed based on
the
crew rotation schedule example in Duane Hookom's
calendar
database. I am creating a utilization report of
operating
rooms and I am grouping by date then by tim and

then

by
or
room. The problem I run into is that I have procedures
that start on one day and run into the next.

surgery starts at 23:30 and goes until 01:30. The VB
script with the formating function that draws the
calendar
line does not like that. How can I get around the
overlapping day problem?




.



.

.

 

[Michael Noblet]

Cool thanks. I was just trying to make sure that was a
working idea before I spent the time

-----Original Message-----
Mike,
You would need to use similar expressions. I don't have the time to work
through the exact syntax but it should work after some trial and error. I
would be particularly careful to not hard-code your times into the
expressions.

--
Duane Hookom
MS Access MVP
--

Duane,

That worked great. Would this be the best way to split
times into blocks? If I am trying to to calulate the
usage for a block of 7am to 3 pm and then 3pm to 5pm. If
the procedure runs from 2pm to 4 pm, I need to split it
between the 2 groups.

I would assume the same approach would work, but I could
use a bit of help on the calculations in the SQL statement.

Mike

-----Original Message-----
Look slike that should work
-----Original Message-----
You are absolutely on the right track. I just figured out
a no code
solution.
1) create a table tblDayNums with a single numeric field
[DayNum] and two
records with values 0 and 1
2) Create a query with this SQL. You will need to
substitute your own table
and field names. I used:
tblSchedule
==============
SchDate
SchStartTime
SchEndTime
SchProcedure
SchRoom
SELECT [SchDate]+[DayNum] AS ScheduleDate,
tblSchedule.SchStartTime,
tblSchedule.SchEndTime, IIf([SchEndTime]< [SchStartTime]
And
[DayNum]=1,#12:00 AM#,[SchStartTime]) AS CalcStartTime,
IIf([SchEndTime]<[SchStartTime] And [DayNum]=0,#11:59 PM#,
[SchEndTime]) AS
CalEndTime, tblSchedule.SchRoom,
tblSchedule.SchProcedure, tblDayNums.DayNum
FROM tblDayNums, tblSchedule
WHERE (((tblDayNums.DayNum)<=Abs([SchEndTime]<
[SchStartTime])));

ScheduleDate, CalcStartTime and CalcEndTime should be
used in the report.

--
Duane Hookom
MS Access MVP
--

"Michael Noblet" <[email protected]>
wrote in message
I am thinking that I would need to break the record up.

Case starts on 06/01/2004 at 23:30 and ends on 6/2/2004
at
01:30.

currently the data is populated with a start date, start
time and end time. I was thinking that I would truncate
the origanal record to show 23:30 to 23:59 and then
have a
new record with a start date of 6/2/2004 fro 00:00 to
01:30.

I would prefer to do this in the query, but would be
just
as comfortable doing it with code. Any thoughts on a
procedure to make this happen or am I heading down the
wrong path.

-----Original Message-----
You would have to make sure the lngDuration value +
lngStart don't add up to
greater than your day. Do you think you need to

break
a

record into two
records if it crosses a day end? You would need to do
this in the code or
query. I would certainly not change the way you add or
edit records.

--
Duane Hookom
MS Access MVP


"Michael Noblet"

wrote in message
I have a calendar report that I have designed based
on
the
crew rotation schedule example in Duane Hookom's
calendar
database. I am creating a utilization report of
operating
rooms and I am grouping by date then by tim and then
by
or
room. The problem I run into is that I have
procedures
that start on one day and run into the next.

surgery starts at 23:30 and goes until 01:30.

The
VB

script with the formating function that draws the
calendar
line does not like that. How can I get around the
overlapping day problem?




.



.

.

.