Update record monthly

[catrue]

I hope I can convey what I am trying to figure out. I am at a loss right now. I have a form where I input personnel information. One of the fields is for how many months experience they have with the program. What i would like is for this record to update itself every month. for example, if they initially had 2 months experience, I would enter 2. Every month therafter, that number with change to 3, 4, 5...etc. Not everyone comes to our section with the same experience, so most will have a different starting number.

This information would be seen on the personnel form, and in a report to show experience levels. Other than those two, it is not called upon.

Any suggestions would be so helpful!

Thank You!!

 

[boblarson]

You shouldn't be storing the data like that. You should have the date that they have been qualified and the number of months of experience can be calculated in a query, or on a form or report, at any time by using the DateDiff function.

 

[catrue]

Here is another example that might pose a problem with that. Lets say they start with the program in January. Work on it for 3 months, then move to another section. Their experience then stops when they move, and will restart when/if they come back.

 

[boblarson]

Well, if that is the case then you will need to have a table which is a one-to-many relationship that will let you have a Start and End date and you would need a query to pull the total months based on the DateDiff of all of the fields for the user id.

Storing the calculated data is a violation of normalization rules and having to update each month is not good database design.