Need help with a query (Access 2003)

Topic

New Reply

I am trying to put together a query to calculate TAT (turn around time). This Department has multiple shifts, and operates multiple days (see below).

Day of Week/Shifts Working
Sunday / 2
Monday / 3,1,2
Tuesday / 3,1,2
Wednesday / 3,1,2
Thursday / 3,1,2
Friday / 3,1
Saturday / No Shifts working (currently)

TAT is calculated by counting the total number of days, beginning with Day 0, from when the work becomes available to the day that it is processed. Now come the exceptions…
1. Any work processed by Shift 2 gets an additional day added into its TAT.
2. Any work processed on Saturday and Sunday count would count as one day only.

I have one table that would hold the available date (adate) for work received, another table that would hold the process date (pdate) and the shift that the work was processed on.

Hoping that you can help me put together a query that will look at the day of the week, shift, etc. to calculate number of days to process.

Marie

Reply | Quote

Replies

This looks remarkably like your post 393661. Won’t the solution given there help here too?

Reply | Quote

This does help for the weekdays, but any work processed on Saturday and Sunday count as only 1 day.

Processing days: [tblRender]![insertdate]-[tblReceived]![availdate]-([shiftID]=2)

What can I add to this so that any work processed on Saturday and Sunday would only count as 1 day?

Thanks again!

Reply | Quote

Try this:

Processing days: IIf(Weekday([tblReceived]![availdate], vbMonday) > 5, 1, [tblRender]![insertdate]-[tblReceived]![availdate]-([shiftID]=2))

Reply | Quote

Hans,

Thanks again and again for all of your help.

My query seems to be hung up on “vbMonday”. Help!!

Reply | Quote

Sorry, I wasn’t thinking. vbMonday is a VBA constant, you cannot use it in a query. Replace it by its value 2.

Processing days: IIf(Weekday([tblReceived]![availdate], 2) > 5, 1, [tblRender]![insertdate]-[tblReceived]![availdate]-([shiftID]=2))

Reply | Quote

Hans,

I really appreciate your patience with me

The processing days increase on Sunday, but not on Monday. How can that be changed to reflect the opposite?

Reply | Quote

This one?

IIf(Weekday([tblRender]![insertdate],2)>5,1,[tblRender]![insertdate]-[tblReceived]![availdate]-([shiftID]=2))

Reply | Quote

Hans,

I made a slight modification:

Processing Days: IIf(Weekday([tblRender]![insertdate],2)>6,[tblRender]![insertdate]-[tblReceived]![availdate],[tblRender]![insertdate]-[tblReceived]![availdate]-([shiftID]=2))

This seemed to keep the work processed on Saturday and Sunday at the same number of days.

Thanks again for all of you help! Have a great evening!!!

Marie

Reply | Quote

I thought no work was processed on Saturdays?

Reply | Quote

You’re right…

I hope to be able to use this formula to work in databases that are utilized in different 2 sites that we operate. One site currently does not work in Saturdays, the other does. Even those things are likely to change. That seems to be the norm these days.

Thanks again so much.

Reply | Quote

Good luck with it. The expression works according to your specifications with Weekday(..,2) > 5 for me, not with > 6. I hope it works OK for you.

Reply | Quote

Hans,

I’m back!!!!

I was wondering if you could assist me again with this TAT formula. Currently, it is adding an extra day for work processed on Sundays (because the shiftID is 2). Can this be changed to only look at shiftID 2 for work processed Monday-Friday? I have also been advised that any work processed on Monday, by any shiftID other than 2 would not increment up by 1 day.

Jumping back to your last reply, the reason that I felt the modified formula worked was because it gave me a running total for Processing Days.

Thanks for looking at this

Marie

Reply | Quote

Sorry, I don’t understand. The formula as provided by me only adds 1 for ShiftID=2 on Monday-Friday. You modified it to add 1 for ShiftID=2 on Saturday too. In neither case does it add 1 on Sunday.

Reply | Quote

Is there another way that this could be put together so that it would show total processing days for work processed on the weekend. Ex:
Availdate is Thursday

Work processed that Thursday, by shiftID 1 or 3 would count as day 0. Work processed by shiftID 2 would count as day 1.
Work processed on Friday, by shiftID 1 or 3 would count as day 1. Work processed by shiftID2 would count as day 2.
Work processed on Saturday or Sunday, by any shift, would count as day 3.
Work processed on Monday, by shiftID 1 or 3 would count as day 3. Work processed by shiftID 2 would count as day 4.

Am I going about this the wrong way?

Reply | Quote

Why doesn’t work processed on Saturday have the same count as work processed on Friday, Shift 2? I thought that Shift 2 was more or less equivalent to the following day.

Reply | Quote

Why doesn’t work processed on Saturday have the same count as work processed on Friday, Shift 2? I thought that Shift 2 was more or less equivalent to the following day.

Reply | Quote

Great question, and I wish that I had an answer for you. Someone set the rules a long time ago. I only want to find an easier way to calculate this, and alleviate the manual process that it is today

Reply | Quote

Great question, and I wish that I had an answer for you. Someone set the rules a long time ago. I only want to find an easier way to calculate this, and alleviate the manual process that it is today

Reply | Quote

Copy this function into a standard module:

Function TAT(BegDate As Variant, EndDate As Variant, Shift As Variant) As Integer
Dim WholeWeeks As Variant
Dim DateCnt As Variant
Dim EndDays As Integer

If IsNull(BegDate) Or IsNull(EndDate) Then
Exit Function
End If

BegDate = DateValue(BegDate)
EndDate = DateValue(EndDate)

WholeWeeks = (EndDate – BegDate) 7
DateCnt = BegDate + 7 * WholeWeeks

Do While DateCnt < EndDate
Select Case Weekday(DateCnt)
Case 2 To 5
EndDays = EndDays + 1
Case 6
EndDays = EndDays + 2
End Select
DateCnt = DateCnt + 1
Loop

If Shift = 2 And Weekday(DateCnt) 1 And Weekday(DateCnt) 7 Then
EndDays = EndDays + 1
End If

TAT = 6 * WholeWeeks + EndDays
End Function

and change the expression in the query to

Processing_Days:TAT([tblReceived].[availdate],[tblRender].[insertdate],[ShiftID])

Reply | Quote

Hello Hans!!

Sorry for the late reply. This function is fabu!! Thanks so much for your help..again.

On a related subject, is there a book that you would recommend for VBA beginners? I have only written code for error handling, etc., and this is new territory for me.

Thanks again!

Marie

Reply | Quote

Beginning Access 2002 VBA from Wrox has a good reputation (the 2003 version is not out yet, but the differences between Access 2002 and 2003 are slight.)

Reply | Quote

Beginning Access 2002 VBA from Wrox has a good reputation (the 2003 version is not out yet, but the differences between Access 2002 and 2003 are slight.)

Reply | Quote

Hello Hans!!

Sorry for the late reply. This function is fabu!! Thanks so much for your help..again.

On a related subject, is there a book that you would recommend for VBA beginners? I have only written code for error handling, etc., and this is new territory for me.

Thanks again!

Marie

Reply | Quote

Copy this function into a standard module:

Function TAT(BegDate As Variant, EndDate As Variant, Shift As Variant) As Integer
Dim WholeWeeks As Variant
Dim DateCnt As Variant
Dim EndDays As Integer

If IsNull(BegDate) Or IsNull(EndDate) Then
Exit Function
End If

BegDate = DateValue(BegDate)
EndDate = DateValue(EndDate)

WholeWeeks = (EndDate – BegDate) 7
DateCnt = BegDate + 7 * WholeWeeks

Do While DateCnt < EndDate
Select Case Weekday(DateCnt)
Case 2 To 5
EndDays = EndDays + 1
Case 6
EndDays = EndDays + 2
End Select
DateCnt = DateCnt + 1
Loop

If Shift = 2 And Weekday(DateCnt) 1 And Weekday(DateCnt) 7 Then
EndDays = EndDays + 1
End If

TAT = 6 * WholeWeeks + EndDays
End Function

and change the expression in the query to

Processing_Days:TAT([tblReceived].[availdate],[tblRender].[insertdate],[ShiftID])

Reply | Quote

Is there another way that this could be put together so that it would show total processing days for work processed on the weekend. Ex:
Availdate is Thursday

Work processed that Thursday, by shiftID 1 or 3 would count as day 0. Work processed by shiftID 2 would count as day 1.
Work processed on Friday, by shiftID 1 or 3 would count as day 1. Work processed by shiftID2 would count as day 2.
Work processed on Saturday or Sunday, by any shift, would count as day 3.
Work processed on Monday, by shiftID 1 or 3 would count as day 3. Work processed by shiftID 2 would count as day 4.

Am I going about this the wrong way?

Reply | Quote

Sorry, I don’t understand. The formula as provided by me only adds 1 for ShiftID=2 on Monday-Friday. You modified it to add 1 for ShiftID=2 on Saturday too. In neither case does it add 1 on Sunday.

Reply | Quote

Hans,

I’m back!!!!

I was wondering if you could assist me again with this TAT formula. Currently, it is adding an extra day for work processed on Sundays (because the shiftID is 2). Can this be changed to only look at shiftID 2 for work processed Monday-Friday? I have also been advised that any work processed on Monday, by any shiftID other than 2 would not increment up by 1 day.

Jumping back to your last reply, the reason that I felt the modified formula worked was because it gave me a running total for Processing Days.

Thanks for looking at this

Marie

Reply | Quote

Good luck with it. The expression works according to your specifications with Weekday(..,2) > 5 for me, not with > 6. I hope it works OK for you.

Reply | Quote

You’re right…

I hope to be able to use this formula to work in databases that are utilized in different 2 sites that we operate. One site currently does not work in Saturdays, the other does. Even those things are likely to change. That seems to be the norm these days.

Thanks again so much.

Reply | Quote

I thought no work was processed on Saturdays?

Reply | Quote

Hans,

I made a slight modification:

Processing Days: IIf(Weekday([tblRender]![insertdate],2)>6,[tblRender]![insertdate]-[tblReceived]![availdate],[tblRender]![insertdate]-[tblReceived]![availdate]-([shiftID]=2))

This seemed to keep the work processed on Saturday and Sunday at the same number of days.

Thanks again for all of you help! Have a great evening!!!

Marie

Reply | Quote

This one?

IIf(Weekday([tblRender]![insertdate],2)>5,1,[tblRender]![insertdate]-[tblReceived]![availdate]-([shiftID]=2))

Reply | Quote

Hans,

I really appreciate your patience with me

The processing days increase on Sunday, but not on Monday. How can that be changed to reflect the opposite?

Reply | Quote

Sorry, I wasn’t thinking. vbMonday is a VBA constant, you cannot use it in a query. Replace it by its value 2.

Processing days: IIf(Weekday([tblReceived]![availdate], 2) > 5, 1, [tblRender]![insertdate]-[tblReceived]![availdate]-([shiftID]=2))

Reply | Quote

Hans,

Thanks again and again for all of your help.

My query seems to be hung up on “vbMonday”. Help!!

Reply | Quote

Try this:

Processing days: IIf(Weekday([tblReceived]![availdate], vbMonday) > 5, 1, [tblRender]![insertdate]-[tblReceived]![availdate]-([shiftID]=2))

Reply | Quote

This does help for the weekdays, but any work processed on Saturday and Sunday count as only 1 day.

Processing days: [tblRender]![insertdate]-[tblReceived]![availdate]-([shiftID]=2)

What can I add to this so that any work processed on Saturday and Sunday would only count as 1 day?

Thanks again!

Reply | Quote

This looks remarkably like your post 393661. Won’t the solution given there help here too?

Reply | Quote