First and Second Shift Interpretation

[dansalmada]

Hi,

In my company there is a day shift from 7 AM to 5 PM and a night shift from 7 PM to 5 AM. The problem is that the night shift extends to two separate days.

What can I do to filter by shift if I have a list of all the dates with the hours? Is there a way to do this?

Thanks

 

[KenHigg]

What is the relevent structure of the table. What does one record represent?

 

[dansalmada]

What I have is a table where the fields are: date, production line, production in pieces.

I need to identify if it was day - shift #1, day - shift #2

For example: Line 1 produced 200 pieces from 7 am to 5 pm that is shift 1 Monday. But it produced 200 pieces from 7 pm to 5 am (shift 2) but it includes part of Tuesday as well but I need it to be shift #2 Monday.

Have I explained myself?

 

[KenHigg]

And may I assume that when a record is added, for the date field, the user could enter something 6/30/2004 08:00. (?)

 

[dansalmada]

exactly!!! =)

 

[KenHigg]

Q. If a record gets entered for 6/30/18 01:30, does it actually get recorded as being part of second shift for the 17th?


(Sorry to be back & forth...)

 

[dansalmada]

Nope, that is exactly what I need to identify

 

[KenHigg]

Now I'm confused. It would appear that your business rules are in need of a re-evaluation.

If I ask you for second shift data, one of two scenarios can occur:

1. If I work before midnight, all the data gets reported on the next day,
(for the sake of simplicity, lets call this 'Posting Forward')

Or

2. If I work after midnight, all the data gets reported on the previous day
(for the sake of simplicity, lets call this 'Posted Backward')
Or

3. My numbers from 7 till midnight get posted on one day, then my numbers from Midnight till 7am get posted on another day. In which case, if I wanted to get a total production for a shift for a day, I would have to find and add the two numbers.

Which is it?

 

[Mike375]

You mentioned that you have a time entered which I assume is the start of the shift. So would those times all be in the 5am to 6am braket and the 7pm to 8pm braket??

If so, can you add another field to the table and fill the field with 6pm and then create a new field in a query

NewField:IIf([ShiftTime]>[FixedTime],"Shift 2","Shift 1") and assuming that [FixedTime] only has a 6pm entered.

 

[dansalmada]

Example:

Line 1 Produced 500 pieces from 7:00 AM to 5:00 PM (shift 1)
Line 1 Produced 300 pieces from 6:00 PM to 5:00 AM (shift 2)

I need a criteria to separate second shifts because second shifts go over to another day.

E.G. 6:00 PM Monday to 5:00 AM Tuesday morning, but I need to identify it only as night shift Monday.

 

[KenHigg]

I would recomend doing a function or two that would give the results you need. Then use the function in your queries/forms/reports. You pass the function the date/time. The function does the following:

1. Looks at the time part and if it is between 7am and 5pm it returns:

The date and " / Day Shift"

2. If the date is between 7pm and 11:59pm it returns:

The date and " / Night Shift"

3. If the date is between 12am and 5am it returns:

The date - 1 and " / Night Shift"

Of course variations of this could be used if you just need the date or just the shift...

 

[Mike375]

Example:

Line 1 Produced 500 pieces from 7:00 AM to 5:00 PM (shift 1)
Line 1 Produced 300 pieces from 6:00 PM to 5:00 AM (shift 2)

I need a criteria to separate second shifts because second shifts go over to another day.

E.G. 6:00 PM Monday to 5:00 AM Tuesday morning, but I need to identify it only as night shift Monday.

Make a new field in a query

Shifts: IIf([Finish]-[Start]>0,"Shift 1","Shift 2")

Also, if you make another field in your query

WhichDay: Weekday([ShiftDate])

That will give a number from 1 to 7 with 1 being a Sunday

Another new field with the the IIF function will give Monday, Tuesday etc from the day number result.

Mike

 

[KenHigg]

Make a new field in a query

Shifts: IIf([Finish]-[Start]>0,"Shift 1","Shift 2")

Also, if you make another field in your query

WhichDay: Weekday([ShiftDate])

That will give a number from 1 to 7 with 1 being a Sunday

Another new field with the the IIF function will give Monday, Tuesday etc from the day number result.

Mike

Where will you get [Finish] & [Start]?

 

[Mike375]

Where will you get [Finish] & [Start]?

Line 1 Produced 500 pieces from 7:00 AM to 5:00 PM (shift 1)
Line 1 Produced 300 pieces from 6:00 PM to 5:00 AM (shift 2)

 

[KenHigg]

Quote:

'What I have is a table where the fields are: date, production line, production in pieces.'

 

[Mike375]

Quote:

'What I have is a table where the fields are: date, production line, production in pieces.'

Yes I saw that but he appears to be indicating he has more. For example, with the date, is he entering it in the same field and the start and finish of the shift??

From a post of yours:

1. Looks at the time part and if it is between 7am and 5pm it returns:

Where are you getting time from.

Mike

 

[KenHigg]

See posts #4 and #5

 

[Mike375]

See posts #4 and #5

But does he only have one date field and if so does he enter at the start or the finish of the shift or does it get mixed up, that is, might be after Shift 1 and at the start of Shift 2.

A couple of extra fields and labels or buttons for Shift 1 and Shift 2 are the requirements.

Mike

 

[Rich]

A couple of extra fields and labels or buttons for Shift 1 and Shift 2 are the requirements.

Mike

No they aren't

 

[Mike375]

No they aren't

Well why don't you offer dansalmada a solution