# Thread: Need help with a query (Access 2003)

1. ## Need help with a query (Access 2003)

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

2. ## Re: Need help with a query (Access 2003)

This looks remarkably like your <post#=393661>post 393661</post#>. Won't the solution given there help here too?

3. ## Re: Need help with a query (Access 2003)

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!

4. ## Re: Need help with a query (Access 2003)

Try this:

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

5. ## Re: Need help with a query (Access 2003)

Hans,

Thanks again and again for all of your help.

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

6. ## Re: Need help with a query (Access 2003)

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))

7. ## Re: Need help with a query (Access 2003)

Hans,

I really appreciate your patience with me <img src=/S/doh.gif border=0 alt=doh width=15 height=15>

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

8. ## Re: Need help with a query (Access 2003)

This one?

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

9. ## Re: Need help with a query (Access 2003)

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

10. ## Re: Need help with a query (Access 2003)

I thought no work was processed on Saturdays?

11. ## Re: Need help with a query (Access 2003)

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.

12. ## Re: Need help with a query (Access 2003)

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.

13. ## Re: Need help with a query (Access 2003)

Hans,

I'm back!!!! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

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 <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>

Marie

14. ## Re: Need help with a query (Access 2003)

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.

15. ## Re: Need help with a query (Access 2003)

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?

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•