keeping track of 3/24hr shifts 1

[jgonick]

I am trying to keep track of three 24hr shift work days.

The shift starts at 6pm on one day and ends at 5:59 pm the next day. Lets just call the three shifts A, B, and C. I need to be able to enter a time and date and know what shift will be on duty in the future and/or who was on in the past(if the date was in the past). I also need a way to assign a number or date or something (i assume it would have to be based off the date) to each shift to keep every shift unique so I can record other data to that specific shift.

Any ideas?

thanks

 

[PaulBricker]

Can you provide a little more info. Do these shifts go in consecutive order (i.e. A first day, B second day, C third day, A fourth day, B fifth day, C sixth day, A seventh day, B eighth day and so on. What is the starting date you are using and any other relevant info.

Paul

 

[TonyJollans]

Hi jgonick,

If your shifts are always 24 hours then simply using the date they started will provide you with a unique way of identifying them. You might, though, find it more convenient to use 6:00 PM on that date. You could then check, perhaps, for the (unique) record with a date between the time you were interested in and 24 hours earlier. With a little bit of experimentation you could find out which of the three possible values of [YourDate] Mod 3 corresponded to each of your three shifts.

Please post back if you want more help.

Enjoy,
Tony

 

[jgonick]

Paul, Yes the shifts are consecutive. They will never get out of order. Starting date and ending date are sort of irrelevant, I need to go be able to go past, present, and future. Lets say today (Sept.3, 2003 at 6:00pm) will start A shift.

Thanks for all your help..

Tony, that might work. I need to think about it. (I need to let it soak in, sometimes I can be slow )

Thanks

 

[PaulBricker]

I'm not worried about the ending date, but it needs to be determined where the first shift starts so you can use something like
MyShift:IIf(DateDiff("d",#9/3/03#,Date()) Mod 3 = 0, "C",IIf(DateDiff("d",#9/3/03#,Date()) Mod 3 =1,"B","A&quot)

This will give you an A, B or C in a column of your query.
The other question is testing for before or after 6 pm on a given date so is the time separate from the date or is your date field stored a date and time. We'll need that info.

Paul

 

[TonyJollans]

Hi Paul,

Aren't you making it more complicated than it needs to be? What does the DateDiff bring to the party?

Choose((#9/3/03# Mod 3) + 1, "A", "B", "C")

Depending on when the pattern starts it might have to be "B", "C", "A" or "C", "A", "B" but this should provide the shift.

Enjoy,
Tony

 

[PaulBricker]

That's a very nice expression but every day has two shifts associated with it so we still need to add the time of day into the mix to determine which shift it actually is. I will give you your due as far as the two expressions go. Your's is much better in all ways.
Have a star.

Paul

 

[TonyJollans]

Hi Paul,

Thanks for the star.

Agreed, time is critical .. and my formula doesn't take account of it; this should be better if the input has a time (and 6.00 pm is shift start time) ..

ChosenTime = #3 Sep 03 18:00#
ChosenShift = Choose(((ChosenTime + 0.25) Mod 3) + 1, "A", "B", "C")

The second question is different. If records were keyed on shift start time ..

#1 Sep 03 6:00pm#
#2 Sep 03 6:00pm#
#3 Sep 03 6:00pm#
#4 Sep 03 6:00pm#
etc.

.. then, to get the record for a particular time should be something like ..

SELECT Shift.*
FROM Shift
WHERE [Shift].[StartTime] Between ChosenTime - 1 And ChosenTime

Enjoy,
Tony

 

[jgonick]

Thanks, I think I understand. I'll give it a try. If I run into problems I'll be back.

Thanks for the help..

Jgonick