Apply changes from date changed

[mari_hitz]

Hi everyone,



Hope you are ok. I have a question/issue: I have a database that contains a table with all tasks made in our team and the time it takes to perform the task. I have another table with month, year and value in which I put month after month the volumes that each task handles. So, in a query I multiply the time to perform the task and the volume; month after month I have different results. However "the time to perform the task" may vary depending on processes improvements that we made. If before I had 5 minutes to perform the task and now I have 2, I change it and then the results on the query are changed for all months. I do not know if there is a way to specify that that change must be applied from the month where it is updated to the following months and not retroactively. Is this possible?

Regards,

Mari

[ranman256]

You would need a form (a CHANGE TASKS THRU DATE RANGE form)

Boxes to
enter the Task,
the new time, and
the date range it would be effective.

click execute button and a query would update the time between StartDate and EndDate

[rpeare]

if any value in your database changes over time you've got to have a table to support a 'history' of the changes

So for instance instead of having a table like:

TaskID  TaskName  TaskTime
1       Task A    5

and when you change the task from a 5 minute task to a 2 minute task you change the value of this single instance to 2 in the task time you would have a table more like

TaskID  TaskName  TaskTime  TaskEff  TaskTerm
1       Task A    5         1/1/2014 1/5/2014
2       Task A    2         1/6/2014

Then you would pull the appropriate task record when doing your calculations

[ranman256]

Ok the execute macro would 1st run a query post to the history of changes tbl.
THEN correct the times in the main tbl.