|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
July 7th, 2009, 09:00 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
Link Multiple Criteria
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. 
|
|
#2
July 7th, 2009, 09:21 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
Link Multiple Criteria
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 -- _________ Sean Bailey "JD McLeod" wrote: 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.
|
|
#4
July 7th, 2009, 09:30 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
Link Multiple Criteria
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" wrote: 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 -- _________ Sean Bailey "JD McLeod" wrote: 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.
|
|
#5
July 7th, 2009, 10:48 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
Link Multiple Criteria
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. -- _________ Sean Bailey "JD McLeod" wrote: 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" wrote: 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 -- _________ Sean Bailey "JD McLeod" wrote: 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.
|
|
#6
July 9th, 2009, 10:30 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
Link Multiple Criteria
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" wrote: 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 -- _________ Sean Bailey "JD McLeod" wrote: 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.
|
|
#7
July 9th, 2009, 11:59 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
Link Multiple Criteria
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). -- _________ Sean Bailey "JD McLeod" wrote: 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" wrote: 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 -- _________ Sean Bailey "JD McLeod" wrote: 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.
|
|
#8
July 10th, 2009, 01:04 AM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
Link Multiple Criteria
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
