Date and hourly time of use

[Jeff C]

I have the start date, start time and end time for procedures throughout each
day for 13 procedure rooms.

I need to come up with an average number of rooms that are being used for
every hour of the day for each week over a period of three months.

Knowing the start and end times I have the duration of each procedure in a
room. I can indicate the hour of the procedure starting but how to indicate
the additional hours as being in use is where I am stuck.

Ex. Start date is 12/5/07 start time is 2247 and duration is either 276
minutes or 4.6 hours. How would I indicate that the 2300 hour, 0000 hour,
0100 hour, 0200 hour, and 0300 hour (on 12/6/07) are in use?

I was thinking of some kind of if statement with yes/no fields but I still
need a date calculation to come up with values to base the if statement on.

Any creative minds have an idea on this???

Thanks in advance.

 

[KARL DEWEY]

Create a table named CountNumber with field called CountNUM having integers
from 0 (zero) through your maximum.
Rename your fields to StartDate and EndDate and make them DateTime datatype
or use a query that reformats your data to DateTime data.

SELECT JeffC.Room, JeffC.StartDate,
Format(DateAdd("h",[CountNUM],[StartDate]),"h d mmm yyyy") AS [Hours in use]
FROM JeffC, CountNumber
WHERE (((DateAdd("h",[CountNUM],[StartDate]))<=[EndDate]));

 

[Jeff C]

--
Jeff C
Live Well .. Be Happy In All You Do

Create a table named CountNumber with field called CountNUM having integers
from 0 (zero) through your maximum.

I built the query but am having issues with the wrong data type in the
criteria field.

I have a table with [CountNUM]![CountNUM] with 15 records (0-14)

Rename your fields to StartDate and EndDate and make them DateTime datatype
or use a query that reformats your data to DateTime data.

[JeffC]![Room], [JeffC]![StartDate], [JeffC]![EndDate]

SELECT JeffC.Room, JeffC.StartDate,
Format(DateAdd("h",[CountNUM],[StartDate]),"h d mmm yyyy") AS [Hours In Use]
FROM JeffC, CountNUM;

I am not using the field of the procedure length in hours (integer) in table
[JeffC]???

Thanks for your help with this, where am I going wrong?

Start Date and EndDate are formatted for long time - 12/3/2007 10:30 AM

SELECT JeffC.Room, JeffC.StartDate,
Format(DateAdd("h",[CountNUM],[StartDate]),"h d mmm yyyy") AS [Hours in use]
FROM JeffC, CountNumber
WHERE (((DateAdd("h",[CountNUM],[StartDate]))<=[EndDate]));

--
KARL DEWEY
Build a little - Test a little

I have the start date, start time and end time for procedures throughout each
day for 13 procedure rooms.

I need to come up with an average number of rooms that are being used for
every hour of the day for each week over a period of three months.

Knowing the start and end times I have the duration of each procedure in a
room. I can indicate the hour of the procedure starting but how to indicate
the additional hours as being in use is where I am stuck.

Ex. Start date is 12/5/07 start time is 2247 and duration is either 276
minutes or 4.6 hours. How would I indicate that the 2300 hour, 0000 hour,
0100 hour, 0200 hour, and 0300 hour (on 12/6/07) are in use?

I was thinking of some kind of if statement with yes/no fields but I still
need a date calculation to come up with values to base the if statement on.

Any creative minds have an idea on this???

Thanks in advance.

 

[KARL DEWEY]

As I said you have to either redo your tables to what I put in the query I
posted or preceed that query with one that translates your fields -- Start
date, start time, and duration into two fields.

StartDate : CVDate(Format([YourTableName].[StartDate],"m/d/yyyy") & " " &
Left([YourTableName].[starttime],2) & ":" &
Right([YourTableName].[starttime],2))

EndDate:
DateAdd("n",[duration],CVDate(Format([YourTableName].[StartDate],"m/d/yyyy")
& " " & Left([YourTableName].[starttime],2) & ":" &
Right([YourTableName].[starttime],2)))

--
KARL DEWEY
Build a little - Test a little

--
Jeff C
Live Well .. Be Happy In All You Do

Create a table named CountNumber with field called CountNUM having integers
from 0 (zero) through your maximum.

I built the query but am having issues with the wrong data type in the
criteria field.

I have a table with [CountNUM]![CountNUM] with 15 records (0-14)

Rename your fields to StartDate and EndDate and make them DateTime datatype
or use a query that reformats your data to DateTime data.

[JeffC]![Room], [JeffC]![StartDate], [JeffC]![EndDate]

SELECT JeffC.Room, JeffC.StartDate,
Format(DateAdd("h",[CountNUM],[StartDate]),"h d mmm yyyy") AS [Hours In Use]
FROM JeffC, CountNUM;

I am not using the field of the procedure length in hours (integer) in table
[JeffC]???

Thanks for your help with this, where am I going wrong?

Start Date and EndDate are formatted for long time - 12/3/2007 10:30 AM

SELECT JeffC.Room, JeffC.StartDate,
Format(DateAdd("h",[CountNUM],[StartDate]),"h d mmm yyyy") AS [Hours in use]
FROM JeffC, CountNumber
WHERE (((DateAdd("h",[CountNUM],[StartDate]))<=[EndDate]));

--
KARL DEWEY
Build a little - Test a little

I have the start date, start time and end time for procedures throughout each
day for 13 procedure rooms.

I need to come up with an average number of rooms that are being used for
every hour of the day for each week over a period of three months.

Knowing the start and end times I have the duration of each procedure in a
room. I can indicate the hour of the procedure starting but how to indicate
the additional hours as being in use is where I am stuck.

Ex. Start date is 12/5/07 start time is 2247 and duration is either 276
minutes or 4.6 hours. How would I indicate that the 2300 hour, 0000 hour,
0100 hour, 0200 hour, and 0300 hour (on 12/6/07) are in use?

I was thinking of some kind of if statement with yes/no fields but I still
need a date calculation to come up with values to base the if statement on.

Any creative minds have an idea on this???

Thanks in advance.