Risk Assessment database - "rollup risk scores" from workstation to department...

[vbaDev]

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!

 

[KeithG]

Can you post an example db?

 

[vbaDev]

Can you post an example db?

ehm, i'm not sure my company would allow that

i realize that makes it harder, i'm sorry!

a general concept idea would do fine.

Thanks!

 

[KeithG]

You could use the switch function.

 

[David Eagar]

Can you post your table structure? I think the key to your success lays here

 

[KeithG]

Yeah, can you also expand on the calculations. You give an example and then say so forth.

 

[vbaDev]

ok, i've isolated the 3 tables involved in this. The tblAssessments table (left) stores actual assessments (it's like a transaction table). "Level" means either Site, Department or Workstation "scope" of the assessment.

The listRisks table (right) is a fixed list of applicable risks (50 total) which may apply. Each risk has applicable body parts, which may be affected "checked" or "unchecked" (yes/no).

The tblRisks table (middle) drives an Assessment Subform, where specific applicable risks from listRisks are "selected" for the assessment (depending on scope and circumstances). So for each Assessment there are up to 50 records here.

For each of those applicable Risk Records in tblRisks an assessor specifies the following values: Severity (0-10), Probability (0-10) and Control (goes down from 1 to .25 and reduces risk factor if controls are good).

On the form there's a calculated value called "RPN" which is Severity*Probability*Control - so the higher the Severity and/or Probability the higher the "Risk" (RPN), but the better the controls the lower the RPN.

So, each of the subrecords of tblRisks has an RPN. I need that "rolled up" to a total for the whole Assessment.

But then also, for all workstation assessments within a department I'll need those rolled up to dept. and all depts to site.

but for now i'm just trying to find a roll up for the assessment.

is this making sense? I know it may be hard without knowing the context.

THANKS!

 

[David Eagar]

I think it would work better if you retink your primary table. Ultimately, the key driver of the information you want to derive from the db is 'What has / needs to be assessed'? Answer is the Workcentres

With properly set up lookups (sounds like it might be Cascading Combo boxes), these can be assigned to Departments and Sites and the existing assesment procedures can then be linked to the Workcentre

Your rollups should be pretty smooth sailing after that (he states hopefully)

 

[vbaDev]

Actually, given the fact that I took over an already started project (so some of the naming conventions could be better) i think the table structure is correct. Departments and Site can also be assessed individually (separately from workstations).

For now, any ideas on rolling-up individual Risk RPN (Risk Priority Number) scores to a total Assessment score?

 

[vbaDev]

my idea so far was to have a calculated field in the query that drives the risks subform (based on tblRisks) but what i can't figure out is how to access the values in the table of multipliers based on the RPN value.

Multipliers table (I call them "bands") looks like this:

BandID	HighVal   w_factor
1	7.5	2
2	16	4
3	25	6
4	50	8
5	100	10

so RPN of 50 and up to 100 gets multiplied by 10
... RPN of 25 to 50 gets * 8
... RPN of 16 to 25 gets * 6 etc.

I just don't know how to relate these and don't know how to refer to bands conditionally from within SQL.

Thanks!

 

[KeithG]

I would add a lowVal Field and then you can create a join in a query. You would have to write the SQL and not use the query desinger though

 

[KeithG]

Here is an example I made for you. Look at query1

 

[vbaDev]

just for completeness: we solved this problem as follows:

we changed the table of multipliers to include all possible Severity*Probability*Control permutation values (there's only 36 unique values). That way I was able to link this table in a query to anything with RPN in it and use calculated fields to produce weighted rollups (weighted mean).

thanks for the help!