Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Mechanicsville, Virginia, USA
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #3
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Mechanicsville, Virginia, USA
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #5
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Mechanicsville, Virginia, USA
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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!!
    Attached Files Attached Files

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #7
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Mechanicsville, Virginia, USA
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Need help with a query (Access 2003)

    This one?

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

  9. #9
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Mechanicsville, Virginia, USA
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Need help with a query (Access 2003)

    I thought no work was processed on Saturdays?

  11. #11
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Mechanicsville, Virginia, USA
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #13
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Mechanicsville, Virginia, USA
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
    Attached Files Attached Files

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #15
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Mechanicsville, Virginia, USA
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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 LastLast

Posting Permissions

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