Risk management table design help

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

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Rob" <no.spam DeleteThis @aanet.com.au> wrote in message
news:e160pejbvm64.1blieoipzxx6i$.dlg@40tude.net...
>
> 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