If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Day and night patrol hours by day
Hello!
First off, this is a repost of an earlier request with modifications. I have data set up in the following format: Area | Start | Stop Area is one of five areas in a River. Start is the start time/date of the patrol. Stop is the stop time/date of the patrol. From this data I would like to create a query which shows how many hours were patrolled during the day and during the night for each day in each area. The daytime hours would run from 06:00 to 18:00 each day with night hours taking up the other 12 hours. Thanks for any help! Matthew |
#2
|
|||
|
|||
Day and night patrol hours by day
Hi,
It would be a complex expression, mainly because we have to extract the time part, compare it, then add it to the date part. To get the day-time, CorrectedStart: DateValue(start) + iif( TimeValue(start) #06:00:00#, TimeValue(start), #06:00:00#) CorrectedEnd: DateValue(start) + iif( TimeValue(stop) #18:00:00#, TimeValue(stop), #18:00:00#) TentativeTime: CorrectedEnd - CorrectedStart DayTime: iif( TentativeTime 0, TentativeTime, 0) To get the pre-morning time (from 00:00:00 to 06:00:00) change the limits. To get the evening time, use 18:00:00 and 23:59:59 limits. To get the night time, add the pre-morning time to the evening time. Example: (for day time) Start= x @ 2:00:00; end = x @ 4:00:00 == CorrectedStart= x @ 6:00:00; CorrectedEnd = x @ 4:00:00, Tentative = -2h, DayTime = 0- Start = x@ 19:00:00, end = x+1 @ 3:00:00 == CorrectedStart = x @ 19:00:00 CorrectedEnd = x@ 18:00:00, tentative = -1h, DayTime = 0 Start = x @ 3:00:00, end = x@ 7:00:00 == CorrectedStart = x @ 6:00:00, CorrectedEnd = x @ 7:00:00, tentative = 1h, DayTime = 1h Hoping it may help, Vanderghast, Access MVP "Matthew P" wrote in message ... Hello! First off, this is a repost of an earlier request with modifications. I have data set up in the following format: Area | Start | Stop Area is one of five areas in a River. Start is the start time/date of the patrol. Stop is the stop time/date of the patrol. From this data I would like to create a query which shows how many hours were patrolled during the day and during the night for each day in each area. The daytime hours would run from 06:00 to 18:00 each day with night hours taking up the other 12 hours. Thanks for any help! Matthew |
#3
|
|||
|
|||
Day and night patrol hours by day
Need to understand your data a bit more in terms of the entry of Start and
Stop Do Start/Stop times overlap the day period/night period? That is, could you have a record that would be Area: 1 Start: January 1, 2006 08:00 AM End: January 1, 2006 07:00 PM Do Start/Stop times cover extended periods of more than 24 hours? Example Area: 1 Start: January 1, 2006 08:00 AM End: January 3, 2006 07:00 PM If not then you may be able to adjust the time for calculation purposes by subtracting 6 hours and then calculating the day shift durations. "Matthew P" wrote in message ... Hello! First off, this is a repost of an earlier request with modifications. I have data set up in the following format: Area | Start | Stop Area is one of five areas in a River. Start is the start time/date of the patrol. Stop is the stop time/date of the patrol. From this data I would like to create a query which shows how many hours were patrolled during the day and during the night for each day in each area. The daytime hours would run from 06:00 to 18:00 each day with night hours taking up the other 12 hours. Thanks for any help! Matthew |
#4
|
|||
|
|||
Day and night patrol hours by day
Hello,
Thanks for the quick response to this question. This looks like a much easier solution than what I was expecting to have to do, thank you for that. Just about to start testing to see if it all works out. Matthew Hi, It would be a complex expression, mainly because we have to extract the time part, compare it, then add it to the date part. To get the day-time, CorrectedStart: DateValue(start) + iif( TimeValue(start) #06:00:00#, TimeValue(start), #06:00:00#) CorrectedEnd: DateValue(start) + iif( TimeValue(stop) #18:00:00#, TimeValue(stop), #18:00:00#) TentativeTime: CorrectedEnd - CorrectedStart DayTime: iif( TentativeTime 0, TentativeTime, 0) To get the pre-morning time (from 00:00:00 to 06:00:00) change the limits. To get the evening time, use 18:00:00 and 23:59:59 limits. To get the night time, add the pre-morning time to the evening time. Example: (for day time) Start= x @ 2:00:00; end = x @ 4:00:00 == CorrectedStart= x @ 6:00:00; CorrectedEnd = x @ 4:00:00, Tentative = -2h, DayTime = 0- Start = x@ 19:00:00, end = x+1 @ 3:00:00 == CorrectedStart = x @ 19:00:00 CorrectedEnd = x@ 18:00:00, tentative = -1h, DayTime = 0 Start = x @ 3:00:00, end = x@ 7:00:00 == CorrectedStart = x @ 6:00:00, CorrectedEnd = x @ 7:00:00, tentative = 1h, DayTime = 1h Hoping it may help, Vanderghast, Access MVP "Matthew P" wrote in message ... Hello! First off, this is a repost of an earlier request with modifications. I have data set up in the following format: Area | Start | Stop Area is one of five areas in a River. Start is the start time/date of the patrol. Stop is the stop time/date of the patrol. From this data I would like to create a query which shows how many hours were patrolled during the day and during the night for each day in each area. The daytime hours would run from 06:00 to 18:00 each day with night hours taking up the other 12 hours. Thanks for any help! Matthew |
#5
|
|||
|
|||
Day and night patrol hours by day
Hopefully this clarifies things a bit mo
Start / Stop times can overlap between day and night periods but should not be covering greater than 24 hours. The other point I should have noted is that Start/Stops can cross over the midnight time period. For example: Start: September 10, 2006 10:30 PM Stop: September 11, 2006 02:00 AM In this case I would like the 1.5 hours on the 10th to be included with the nighttime patrol hours for the 10th and then 2 hours on the 11th to be included with the nighttime patrol hours there. Currently I am not sure that this can easily be queried out. Please correct me if I'm wrong (I'd much rather do it with one query) but I am thinking I might have to query out records crossing midnight and split them into two patrols each (as in the example below). After this process I could then run the day/night queries. Does this sound right or is there something I'm missing? Example using above patrol times: 1: Start: September 10, 2006 10:30 PM Stop: September 11, 2006 00:00 AM 2: Start: September 11, 2006 00:00 AM Stop: September 11, 2006 02:00 AM Thanks again. "John Spencer" wrote: Need to understand your data a bit more in terms of the entry of Start and Stop Do Start/Stop times overlap the day period/night period? That is, could you have a record that would be Area: 1 Start: January 1, 2006 08:00 AM End: January 1, 2006 07:00 PM Do Start/Stop times cover extended periods of more than 24 hours? Example Area: 1 Start: January 1, 2006 08:00 AM End: January 3, 2006 07:00 PM If not then you may be able to adjust the time for calculation purposes by subtracting 6 hours and then calculating the day shift durations. "Matthew P" wrote in message ... Hello! First off, this is a repost of an earlier request with modifications. I have data set up in the following format: Area | Start | Stop Area is one of five areas in a River. Start is the start time/date of the patrol. Stop is the stop time/date of the patrol. From this data I would like to create a query which shows how many hours were patrolled during the day and during the night for each day in each area. The daytime hours would run from 06:00 to 18:00 each day with night hours taking up the other 12 hours. Thanks for any help! Matthew |
#6
|
|||
|
|||
Day and night patrol hours by day
Thought I would add an update now that I've given it a try.
The statements worked very well, though I did add a couple of modifications myself in order to accomodate the patrols that cross over midnight. I'll just list them briefly below, let me know if you see any obvious flaws or have any comments - definitely still just learning here. For the Morning query I have modified the CorrectedStart to run as follows: CorrectedStart: iif(DateValue(Exited) DateValue(Entered), DateValue(Exited), DateValue(Entered)) + iif(DateValue(Entered)DateValue(Exited), #00:00:00#,iif(TimeValue(Entered) #00:00:00#, TimeValue(Entered), #00:00:00#)) And the Evening as follows: CorrectedEnd: DateValue(Entered) + iif(DateValue(Exited) DateValue(Entered), #23:59:59#, iif(TimeValue(Exited)#12/30/1899 23:59:59#,TimeValue(Exited),#12/30/1899 23:59:59#)) Thank you very much for the help you gave! Matthew "Michel Walsh" wrote: Hi, It would be a complex expression, mainly because we have to extract the time part, compare it, then add it to the date part. To get the day-time, CorrectedStart: DateValue(start) + iif( TimeValue(start) #06:00:00#, TimeValue(start), #06:00:00#) CorrectedEnd: DateValue(start) + iif( TimeValue(stop) #18:00:00#, TimeValue(stop), #18:00:00#) TentativeTime: CorrectedEnd - CorrectedStart DayTime: iif( TentativeTime 0, TentativeTime, 0) To get the pre-morning time (from 00:00:00 to 06:00:00) change the limits. To get the evening time, use 18:00:00 and 23:59:59 limits. To get the night time, add the pre-morning time to the evening time. Example: (for day time) Start= x @ 2:00:00; end = x @ 4:00:00 == CorrectedStart= x @ 6:00:00; CorrectedEnd = x @ 4:00:00, Tentative = -2h, DayTime = 0- Start = x@ 19:00:00, end = x+1 @ 3:00:00 == CorrectedStart = x @ 19:00:00 CorrectedEnd = x@ 18:00:00, tentative = -1h, DayTime = 0 Start = x @ 3:00:00, end = x@ 7:00:00 == CorrectedStart = x @ 6:00:00, CorrectedEnd = x @ 7:00:00, tentative = 1h, DayTime = 1h Hoping it may help, Vanderghast, Access MVP "Matthew P" wrote in message ... Hello! First off, this is a repost of an earlier request with modifications. I have data set up in the following format: Area | Start | Stop Area is one of five areas in a River. Start is the start time/date of the patrol. Stop is the stop time/date of the patrol. From this data I would like to create a query which shows how many hours were patrolled during the day and during the night for each day in each area. The daytime hours would run from 06:00 to 18:00 each day with night hours taking up the other 12 hours. Thanks for any help! Matthew |
Thread Tools | |
Display Modes | |
|
|