Risk Assessment Database - "rollup Risk Scores" From Workstation To Department...

I don't know if it'll make sense out of context like that.

I am working on a Risk Assessment database, where a factory can be assessed to see which risks exist at workstations/departments or the whole facility.

So Assessments can be either a Workstation, a Department or a Site level.

Within each assessment there are multiple risks, each of which receives a numeric "score" (assigned by assessors). Let's say that it's 1-100 scale.

I need a way to "roll up" (or summarize) the scores, so that the whole assessment can display a "total" risk score. But it's not just adding up of the individuals - there are "weighting" multipliers we use, because we want to emphasize high risks.

Also, multiple workstations' rollup Score needs to "roll up" to their department, so that a department's score is "made up" of individual workstation scores. Also using the same weighting multipliers.

Then, the departments are rolled up to the whole facility.

The database has a table of the weighting multipliers so if a Risk Score is 75 or higher, it's to be multiplied by 10, 50-75 - multiply by 8 and so forth.

I can't come up with a way to do this through queries or code.

thanks!