Table Fields to store a Calculated but changing Time Period

[Graybeard]

I need to set up fields which will ultimately calculate a changing time period.Im not worried about the code at this point. Im concerned about the structure of my table. An example will illustrate. All employees are given 180 days to complete a task from a date certain.(the start is different for each employee) My table has a field to store the start date and a field to store the date 180 days later. Ultimately when I create my form the code will calculate the time period. Here is the complication. Every day the employee cant work because it is the Company's fault-ie the equipment breaks down, etc., the employee gets an extra day added to the 180 days. If the employee cant work because its his fault, he doesnt get the time added to the end. Im not worried, at least at this point, about the code to calcultae this changing time period. My question is I need fields in my table to record this time which is or is not added to the end . I can't use one field for each time cateogory because we need a record of each time period which is or is not counted against the 180 days. And I don't want to guess and say "well Ill make a series of 10 fields for each cateogories because thats the max there will be for any employee. Anyone have any suggestions?

 

[sfreeman@co.mer]

Can't you simply increment the bad days field each time it is appropriate?

Add a new field to your table called BadDays, or something similar. Increment that field each time a day should be added. Add that value to your date calculation.

no?

 

[Rich]

You shouldn't store the date completion is due in your table, it's a calculated field and can be returned via a query/calculated control at any time

 

[neileg]

I agree with Rich, but there's more.

You should add a table that stores the 'extra days' information holding the task ID, the employee ID the number of days to add for this stoppage and the reason. Then, as Rich says, calculate the end date by summing the extra days records and using DateAdd to calculate the end date. Since you can have as many, or as few, records per employee and task as you need, there's no preset limit.

 

[Graybeard]

Thanks Neil and Rich. I will create a seperate table but I do have a another question.We are going to want to generate reports every week showing what completion dates are up that week. If I dont put the completion date field in my table how will be able to run our report?

 

[The_Doc_Man]

Whenever you have a report that needs to include a calculation, always run it from a query (which can include formulas) based on your tables.

Look up DateAdd function and consider that what you want for each problem is a date-add of a base date plus a number of days determined by 180 + the extra days granted when the delay is company-caused.

 

[Graybeard]

Thanks Doc I will use a query when I go to design my report. I hate to be thickheaded but I am a newbee and there is something I still don't understand. Rich said not to put a field in my table for the final completion date(180 + delay caused by company). I understand that I cant have a calculation in my table but if dont have a field to record the result of the calculation how will I be able to create the query and generate the report? How does the query generate it and how does it appear on the report if ther is no field to record the result of the calculation?

 

[neileg]

You create a new, calculated, field in the query that takes your date and adds on the extra days. You may have to do a nested query, though. Create a query that looks at the extra days table and sums the days for each task. Then create a second query that joins the original task table with the extra days totals query. In this second query, create a new field that adds the extra days total to the original date plus 180.