This is looking pretty good waymond
Procedural approach...
1) Open up the Relationship GUI (From the menu, "Tools" -> "Relationships")
2) Add your five tables.
3) Click to select BusID on the BusinessProcess table and drag it to BusID on the BusSubProcesses Table. A window will popup to allow you to "Edit relationship". Meanings are as follows...
- Enforce referential integrity. This means that the parent has to exist before creating a child. For example, you have to have defined a BusinessProcess before you can define Business SubProcess. It also means that you can not delete a parent (Business process) if a related child exists (Business subprocess). I reccommend to always enforce referential integrity.
- Cascade update related fields. This means that if you change the value of a primary key on the parent, the foreign key of the children are updated. This usually applies to primary keys defined as a text string. For example, if you were using a text string for your Business Process, and used "Bus1", "Bus2". Later you want to change this to "Bus1" -> "Bus01", "Bus2" -> "Bus02". Cascading updates would accomplish this. I usually do not use this option, especially if using numeric numbers for your primary keys, but this is generally not a dangerous thing to enable. (Note: If you have a large table or tables, the cascade updates may take a while to complete)
- Cascade Delete Related Records. Here, if you delete the parent record, any related child records are also deleted. I personally never use this option; I consider this feature dangerous. If the end user wants to delete the child records, I have them deleted these first before allowing them to delete the parent record. However, this is just my opinion. There are others who prefer to enable this option.
If you enable "Enforce referential integrity", you will see a "1" on the "one" side, and an infinity sybol on the "many" of of the relation.
Repeat the above process. Click and select the BusID on the BusinessProcess table and drag it to the other related tables ... BusID on the BusinessRules table.
...Moving on
I still do not understand your Object table and your Function table.
Object table
ObjID PK
Lib *
Name *
Type *
Obj Narrative
With ObjID as the primary key, I do not think it is necessary to list the Lib, Name and Type as the primary key. Is it? Are you trying to ensure that only one Lib + Name + Type exist? You can do this by creating a unique index as follows. Open the Object table in design mode. Then, from the menu, "view" -> "Indexes ". You can define a unique index as depicted in the following example...
[tt]
IndexName FieldName SortOrder
UniqueObj Lib Ascending
Name
Type
[/tt]
For UniqueObj, set the Index properties "Unique" to "yes".
The next thing I do not understand is how the Object table will relate to the Business process or subprocess. Does it? And will you be able to restrict the Object.Lib and Object.Type to within your expectations?
I have a similar issue with the
Function table
FuncID PK
Func Narrative Memo
This is a much simpler table than the Object table, but does it / how does it relate to the Business Process and Subprocess?
If a Business process can have a function, but it is not mandatory, then you can do something like...
BusinessProcess table
Fields
BusID PK
Narrative Memo
FuncID - foreign key t function table
Note that the function field does not have to be filled.
However, in your Relationships, you do want to relate (click and drag) the FuncID in the BusinessProcess table to the FuncID in the Function table (and enforce referential integrity).
If a Business SubProcess can also have a function, but not mandatory, repeat the above process.
If applicable, ditto for objectives.
After you have created your relationships, when you create a form / subform, Access will automatically link the form and subform based on the relationship.
Hope we are getting warmer.
Richard
