Oct 2 2009, 02:51 PM
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?)
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.
Oct 2 2009, 03:03 PM
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...
Oct 8 2009, 10:07 AM
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.
Edited by: AccessWill on Thu Oct 8 11:08:11 EDT 2009.
Oct 8 2009, 11:27 AM
Should be structured like this:-
RiskCriteria - PK - Auto
Hope this helps?
Oct 8 2009, 02:01 PM
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 :
Null : No
Indexes are 3 lines with the primary key.
Edited by: AccessWill on Thu Oct 8 15:02:07 EDT 2009.
Oct 14 2009, 11:40 AM
Congratulations, Will. Mike and I are happy to help. Good luck with your project.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here