Risk management table design help

[Rob]

Hi all,

I am trying to develop a table structure to allow risk management. I have
developed a structure, however all queries I have developed on the
structure do not allow me to populate the tables when it is used as a
recordset for a form. I am sure that I am missing something simple, but I
seem to be missing it at the moment. Here is my current structure (the
risk is related to a task):

tblRisks
riskID - PK
taskIDFK - FK: to tblRisk
hazardTitle - Free field description of Hazard
likelihoodIDFK - FK: To tblLikelihood

tblRiskJoin
riskIDFK - FK: to tblRisks
categoryIDFK - FK: to tblCategory
consequenceIDFK - FK: to tblConsequence
(The three fields together are the PK)

tblCategory:
categoryID - PK
categoryTitle - Free title field (records currently Cost and Schedule)

tblConsequece
consequenceID
consequenceTitle - Text field (records currently Critical, Major and Minor)
consequenceValue - Number

tblLikelihood
likelihoodID - PK
likelihoodTitle - Text field (records currently Likely, Unlikely, Improb)
likelihoodValue - Number

The intention is to query this structure to obtain the consequence and
likelihood values for further calculation. I can obtain the values, but as
previously stated, I cannot use this query as a recordsource in a form to
add risks.

Any help, guidance, examples, comments or criticism welcomed!

Thanks in advance
Rob

 

[Allen Browne]

Instead of doing all this in one form, you will need a form with a subform.

From the structure you describe, it looks like you would want a main form
bound to tblRisks. This form might use combos for the foreign key fields
(taskIDFK and linklihootIDFK), and a subform bound to tblRiskJoin.

The subform would show the combinations of category+conseqnce that apply to
the risk in the main form. Again, the foreign keys (categoryIDFK and
consequenceIDFK) could be combos.

That may not be exactly what you need, but it illustrates the kind of
approach to take.

 

[Rob]

Hi Allan,

Thanks for your response. I think that I will pursue your recommendation.
The reason that I wanted all in one form was the result of the query was
already a subform of my main Task Form. I wanted this (that is the
subform) to the editable, and did not want to go to a 'third' subform
layer.

I think that what I will do is to make the existing subform populate as
'read only', and then allow users to add/edit risks through another form
based on the structure you have suggested,

Thanks again for your response,
Cheers
Rob