Results 1 to 8 of 8
  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 dates (Access 97)

    I am putting together a db to track turnaround times for work produced. The shop works 24/7, with 4 different shifts.
    1st Shift Mon-Fri 7am-3pm
    2nd Shift Mon-Fri 3pm-11pm
    3rd Shift Tues-Sat 11pm-7am
    4th Shift Sat & Sun 7am-7pm

    We have files that have 3 business days to be turned over. Files processed on 2nd Shift are counted as being processed the next day (for TAT purposes only). Any files processed on the weekends are counted as taking only 1 day.

    I need some help trying to put together a query. Please let me know what else you need from me.

    As alway, I really appreciate your assistance!

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

    Re: Need Help with dates (Access 97)

    Could you provide an example of the data you have, and of the result you expect from the query?

  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 dates (Access 97)

    I have 2 tables

    tblReceived
    FileID (name of file) text field (ex. 17R03.OUT)
    AvailDate (date that file was available for processing) date field (ex. 07/20/04)
    Volume (total letters in file) number field (ex. 50,000)

    tblRender
    FileID (from tblReceived) (ex. 17R03.OUT)
    Insert Date (actual date the file was processed) date field (ex. 07/22/04)
    Processed Volume (total processed from fileID) number field (ex. 10,000)
    shift (shift working when file processed) text field (ex. 2nd Shift)

    When calculating a turnaround time for this work produced, it would actually be 3 days instead of 2 because the work was processed on 2nd Shift. If another 10,000 letters had been processed that same day, by 1st Shift, that portion should reflect only 2 days to process.

    Does this help?

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

    Re: Need Help with dates (Access 97)

    So what you want to calculate is turnaround time?

    Can there be more than one record for the same FileID in tblRender?
    If so, how should these be treated?
    If not, why is there a separate table tblRender?

  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 dates (Access 97)

    Hans,

    I have attached a mocked up version. This is Access 2003 (what I use from home). The query would hopefully reflect 3 days to process for the file inserted on 2nd shift.

    Thanks again!
    Attached Files Attached Files

  6. #6
    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 dates (Access 97)

    There can be more than one record for the same fileID in this table. This is because one file can be inserted over various shift, and on different days before it is completed.

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

    Re: Need Help with dates (Access 97)

    Try this expression:

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

    [shiftID]=2 is a Boolean expression, it is either True or False. True has a numeric value of -1, False of 0. So if shiftID equals 2, we subtract -1 from the date difference, in other words, we add 1. If shiftID does not equal 2, nothing is subtracted/added.

  8. #8
    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 dates (Access 97)

    Great!!! Thanks again

Posting Permissions

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