Link Multiple Criteria

[JD McLeod]

I am creating a database for risk management. In this database, users will
input risks and the controls that mitigate those risks. The problem I am
having in designing the table is that one control may mitigate more than one
risk. I envision a table for the risks which contains all of the details
such as risk type, risk category etc. I then thought I would have a table
for the controls and related data such as performance frequency, person
responsible, etc. Where I am stuck is on how to “assign†(link) one of the
controls to one or more risks. I am just beginning to look at the table
design, so any help or thoughts on how to accomplish this would be
appreciated.

 

[Beetle]

It sound like you have, essentially, a many-to-many
relationship between Risks and Controls, in which case
you need a third table to manage it. Example;

tblRisks
******
RiskID (Primary Key)
RiskName
other attributes of each Risk

tblControls
********
ControlID (PK)
ControlName
PersonID (Foreign Key to tblPersons)
other attributes of each Control

tblRiskControls
***********
RiskID (FK to tblRisks)
ControlID (FK to tblControls)
any attributes that are specific to the relationship

(RiskID and ControlID would be a combined PK for the above table)

tblPersons
********
PersonID(PK)
FirstName
LastName
Title
other attributes of each person

 

[Steve]

It sounds like you need multiple tables:

TblRiskType
RiskTypeID
RiskType

TblRiskCategory
RiskCategoryID
RiskCategory

TblRisk
RiskID
RiskTypeID
RiskCategoryID
RiskDesc

TblPerson
PersonID
PersonFirstName
PersonLastName

TblRiskControl
RiskControlID
RiskControlDesc

TblPerformanceFrequency
PerformanceFrequencyID
PerformanceFrequency

TblRiskMitigation
RiskMitigationID
RiskID
PersonID
RiskControlID
PerformanceFrequencyID


Steve
(e-mail address removed)

 

[JD McLeod]

Thank Sean. The table structure you described makes sense. Can you
elaborate on the mechanics of how i would populate the third table to "create
the link"? Would i use a form that pulls data from the other two tables? I
looked up this topic in Access help, but it didn't really explaon how the
third table would work. thanks for your help.

 

[Beetle]

For example, if you wanted to enter/view a Risk and
then assign all the associated Controls, you would create
a main form based on tblRisks with a sub form based
on tblRiskControls (the junction table) using RiskID as
the Master/Child link. In the sub form you would use a
combo box bound to ControlID in order to enter the
Controls that relate to that Risk. The Row Source of the
combo box would be a query based on tblControls.

 

[JD McLeod]

Can the primary key in tblRisks for RISKID and tblControls ControlID be an
autonumber? Is there a risk that the autonumber may reset and i end up with
duplicates? Or, should i come up with unique IDs for each risk and control,
maybe a combination of letters and numbers. thanks.

 

[Beetle]

You can use Autonumber for those PK fields. The Autonumber
data type will never repeat a value. The corresponding FK
fields in tblRiskControls must be Number (not Autonumber).

 

[Steve]

Number data type, Long Integer Format.

Steve
(e-mail address removed)