Need Help with dates (Access 97)

Topic

New Reply

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!

Reply | Quote

Replies

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

Reply | Quote

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?

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

Great!!! Thanks again

Reply | Quote

Great!!! Thanks again

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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?

Reply | Quote

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?

Reply | Quote

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!

Reply | Quote

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!

Reply | Quote

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

Reply | Quote