B
batman07
Ok, I am rather a novice with Microsoft Access and need to ask for
some guidance/advice.
My source data is formatted as Long Date. It contains Mon - Sun,
shift start and stop.
I am trying to build a query to determine the following items:
1. Daily shift duration. I have this partially figured out. It
gives me the duration as a whole number and I need it as a time. DAY_0
is Monday.
MDuration: IIf(DateDiff("n",[DAY_0_START],[DAY_0_STOP])>1,DateDiff("n",
[DAY_0_START],[DAY_0_STOP])/60,DateDiff("n",[DAY_0_STOP],
[DAY_0_START])/60)
This removes 30 minutes for a lunch break, if the shift is over 8hrs.
Mon Duration: IIf([MDuration]>8,[MDuration]-0.5,IIf([MDuration]>1,
[MDuration],0))
The "0" if the duration is Null allows me to sum the weekly hours.
2. If more than 50% of the hours fall after 5:00pm. To do this in
Excel, I divided the daily shift duration in half, added the half to
the shift start time and if the total was > 5:00pm (.708333), it
qualified as 1st shift. did the same for these:
Shift Code:
1. Less than 50% of hours after 5pm is shift 1.
2. 50% or more of hours after 5pm is shift 2.
3. 50% or more of hours from midnight to 6am is shift 3.
5. Double day weekends for a 8x5, 16 hours (or 40% of hours on the
weekend) is shift 5.
6. 40% of hours from 9pm Fri to 8am Mon is shift 6.
9. Double day weekends for a 10x4, 20 hours (or 50% of hours on the
weekend) is shift 9.
0. 50% of hours from 9pm Fri to 8am Mon is shift 0.
Where Excel will recognize time as a Serial number, I was able to
build it in Excel. However, I cannot work with times as easy in
Access and have no knowledge of SQL. Can anyone give me some basic
principles or tips as to how I should go about this?
Any and all help is greatly appreciated!
some guidance/advice.
My source data is formatted as Long Date. It contains Mon - Sun,
shift start and stop.
I am trying to build a query to determine the following items:
1. Daily shift duration. I have this partially figured out. It
gives me the duration as a whole number and I need it as a time. DAY_0
is Monday.
MDuration: IIf(DateDiff("n",[DAY_0_START],[DAY_0_STOP])>1,DateDiff("n",
[DAY_0_START],[DAY_0_STOP])/60,DateDiff("n",[DAY_0_STOP],
[DAY_0_START])/60)
This removes 30 minutes for a lunch break, if the shift is over 8hrs.
Mon Duration: IIf([MDuration]>8,[MDuration]-0.5,IIf([MDuration]>1,
[MDuration],0))
The "0" if the duration is Null allows me to sum the weekly hours.
2. If more than 50% of the hours fall after 5:00pm. To do this in
Excel, I divided the daily shift duration in half, added the half to
the shift start time and if the total was > 5:00pm (.708333), it
qualified as 1st shift. did the same for these:
Shift Code:
1. Less than 50% of hours after 5pm is shift 1.
2. 50% or more of hours after 5pm is shift 2.
3. 50% or more of hours from midnight to 6am is shift 3.
5. Double day weekends for a 8x5, 16 hours (or 40% of hours on the
weekend) is shift 5.
6. 40% of hours from 9pm Fri to 8am Mon is shift 6.
9. Double day weekends for a 10x4, 20 hours (or 50% of hours on the
weekend) is shift 9.
0. 50% of hours from 9pm Fri to 8am Mon is shift 0.
Where Excel will recognize time as a Serial number, I was able to
build it in Excel. However, I cannot work with times as easy in
Access and have no knowledge of SQL. Can anyone give me some basic
principles or tips as to how I should go about this?
Any and all help is greatly appreciated!