I aim to create a form to update a risk register.

For each risk, I have to evaluate the risk according to 11 criteria. Each risk has to be evaluated by each 11 criteria only once (a criteria can not missed or evaluated twice). How do I make the relationship between the tables so it's constrained the questions?
Here are my tables.
Risk_table Criteria_Table EvaluationTable
Risk ID (PK) Criteria ID (PK) Risk ID (FK?)
Risk Title Criteria Description Criteria ID (FK?)
Evaluation

I have thought to incorporate the criteria fields in my Risk Table but the problem is that afterwards I have to find the max of the 11 evaluations. This would create a 11 nested IIF (10*9* .... *3*2*1 comparisons). This doesn't seem to be the right approach.

Thanks for your help.
Will

Hi Will,

Welcome to Utter Access!

You can create a compound index to include the riskID and the criteriaID together and set it to not allow duplicates.

Hope that helps...

Hi,

Sorry for the delay. I got other works to do... I just tried it once and I got an error message after creating the index telling that "The changes you requested to the table were not sucessful because they would create duplicate in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicates entries and try again."
I am not familiar with index. Could you explain it a little more. Here is a better view of my DB.

Risk_table
Risk_ID ->PK
Risk_Title

Criteria_Table
Criteria_ID ->PK
Criteria_Description

Evaluation_Table
Risk_ID ->FK
Criteria_ID->FK
Evaluation

Edited by: AccessWill on Thu Oct 8 11:08:11 EDT 2009.

Hi

Your table:-

Evaluation_Table
Risk_ID ->FK
Criteria_ID->FK
Evaluation

Should be structured like this:-

Evaluation_Table
RiskCriteria - PK - Auto
Risk_ID ->FK
Criteria_ID->FK
Evaluation

Hope this helps?

mike

Hi,

Thanks everyone.It works fine. I just added the field RiskCriteria as Mike mentioned.
And as DBguy, I added 2 lines in indexes to constraint the duplicates:
Index name: RiskCriteria
Field Name : RiskID and on the second line Criteria ID
set up with :

Primary :No
Unique: yes
Null : No

Indexes are 3 lines with the primary key.

Thanks
Will

Edited by: AccessWill on Thu Oct 8 15:02:07 EDT 2009.

Congratulations, Will. Mike and I are happy to help. Good luck with your project.