Storing Calculated Fields in a Table

[ckirmser]

I know that it is bad form to store a calculated field in a table - normalization and all that - but I can see no other way around a need that I have.

I'm trying to make a database to store injury time for employees. If an employee is injured, he can do light work for a certain amount of time, but this light work need not be contiguous. But, the total time on light work must be known so as to see when it is used up.

For example:

Bob is injured on 4/1/06 and goes on light work on 4/2/06 until 4/5/06 (4 days). Bob is then taken off light work until 4/10/06 when he reinjures the same injury. This does not count as a new injury, because it is an aggravation of the old one. So, Bob goes back on light work on 4/10/06 until 4/15/06 (6 days).

So, I need to store Bob's total time on light work for this injury (10 days) so that I can keep a running total to check against the maximum for a single injury. All I can think of is to store the sum of days on light work as a field in Bob's injury record, but that means storing a calculated field in a table.

Am I missing an easy way to do this, or is there a method to do what I want that I am just not aware of?

Thanx for any help!

 

[DrSnuggles]

I take it you have a table full of employees and another table that records all the injuries they incur. You can thus have an ID stored in here for the same injury name.

Thus you can do a Groupby, use the DateDiff to calculate the total number of days. Then query on this query for the total off due to this Injury (Using Sum function in the Query).

 

[ckirmser]

I take it you have a table full of employees and another table that records all the injuries they incur. You can thus have an ID stored in here for the same injury name.

Actually, no. The injury itself is not important, only that one was injured.

I have added a field, an ID number assigned to a record when the very first light work assignment is made, but nothing else.

I had thought of some sort of complex query search tying together the ID, date of injury and employee number in order to do a search, but that seemed overly cumbersome.

Thus you can do a Groupby, use the DateDiff to calculate the total number of days. Then query on this query for the total off due to this Injury (Using Sum function in the Query).

This sounds good and I'll have to fiddle with it to see what I can come up with. Thanx!