database relationships help 1

[waymond]

I have these tables

BusinessProcess table
Fields
BusID PK
Narrative Memo

BusSubProcesses Table Business Process can have more than one BusSupProcess.
Fields
BusSubID
BusID
BusSubNarrative

Object table An object can be related to many SubbusinessProcesses.
Fields
ObjID PK
Lib PK
Name PK
Type PK
Obj Narrative

Function A Function can be related to many objects.
Fields
FuncID PK
Func Narrative Memo

BusinessRules Business rules can be more than one rule for the business Process

Fields
BusID
BusRuleID
BusRuleNarrative

I am being asked to create a data entry form so that the data entry person can easily enter data into the fields. I need help on the relations I should use in access to make this possible.

Waymond
thank you in advance

 

[willir]

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

 

[waymond]

The Object table relates to the Business sub process for instance they have as400 files and rpg programs so for a business process say sales a subbusiness process could be update cash the object that relates to the subprocess could be AR Night every object must have a function associated with it. Really what they call functions we call business areas so for ARnight the function could be accounting.

thank you willir