To whom:


I am creating a db for my wife who must track errors in her lab. There are two types of people in her lab: technicians and assistants. She is trying to capture errors for each. For both types of employee, there are four categories of errors: life-threatening, high, medium and low. Within each of the four categories are specific errors to track. I think I have the tables structure for the above figured out.

Now comes the fun part ... After so many errors an employee is terminated. It is not an even graduated scale. The more errors a person gets, the more points each one is worth.

For example:
Low Error 1 - 10 are worth 1 point.


Low error 11-20 are worth 2 points.
Low Error 21-28 are worth 5 points.
Low Error 29-30 are worth 10 points

Medium Error 1-5 are worth 2 points
Medium error 6-13 are worth 5 points
Medium error 14-18 are worth 10 points.

High Error 1-2 are worth 5 points
High error 3-4 are worth 10 points
High error 5-6 are worth 15 points.
High error 7-8 are worth 20 points.

Life Threatening error 1 is worth 10 points.
Life Threatening error 2 is worth 20 points.
Life Threatening error 3 is worth 30 points.
Life Threatening error 4 is worth 40 points.

Each employee could have any combination of the four categories.

What I need is a report that lists all of the errors by employee and totals them by employee. (The report is over a rolling one year period so I only need to add up errors that are less than 1 year old)

My question is, would I write a query to call this information or is it a series of big, hairy IIf statements in a report? Not even sure where to start with this one.

Any thoughts, links, potential solutions would be much appreciated.

Doug