multi tables in access 1

[waymond]

I have requirements to produce a business process table a sub business process table an object table(which are programs) a function table and a business rule table.

Here is the requirement a business process can have many or no sub processes, a object(program can relate to many sub processes, A program can have many functions that support it, also the business rule table has to be linked to the business process table. All tables must have a narrative field where they can put in the narrative for the process or the function or the object(program).

I tried linking these tables many ways and no luck. they want a form that will show them the narratives like Business process and show all the sub processes and narratives and the program narratives that support the business sub process and the function narratives that support the program then they want to see the business process narrative. You could have one process with many programs narratives and function narratives and so forth. Please help

 

[willir]

Hello again waymond

First the basics. Have you had a chance to review the following...
Fundamentals of Relational Database Design by Paul Litwin
Download document
Read on-line (HTML)

Micro$oft's answer...
Where to find information about designing a database in Microsoft Access
283878 - Description of the database normalization basics
304467 - ACC2000 Defining Relationships Between Tables in a Microsoft Access Database

Over all, you seem to want...
[tt]
Business process --> sub processes (and narratives?)

object --> sub processes

program --> functions
[/tt]

business process can have many or no sub processes

Okay, this appears to be a one-to-many relationship.

Your business process will have a field used as the primary key.
The subprocess will include this field this as a foreign key

a object(program can relate to many sub processes

Again, seems to be one-to-many
So your subprocess table will again have to have a field that stores the foreign key that points to the primary key of the object table.

A program can have many functions

Again, seems to be a one-to-many.
I am not sure how programs and functions relate to the business process and subprocesses.

business rule table has to be linked to the business process table

Although the rules are defined on the Rules table, is this simply a value for the Business process, or can a Business Process have more than one rule?

All tables must have a narrative field where they can put in the narrative for the process or the function or the object(program).

Can you a text field with 255 characters, or would a memo field (65k characters) be more appropriate? Or is a "narrative" something different?

I tried linking these tables many ways and no luck

What does not work?
Have you created your relationships?
Remember that you can only link fields that are of the same data type. For example, Text (15 characters) to Text (15 characters), or Integer to Integer.

they want a form that will show them the narratives ....

This is your desired product. Presentation should be given some thought. Perhaps a main form depicts the Business Process. A subform on the right depicts the associated subprocesses. In an adjacent left subform, the narratives are displayed for the selected subprocess. Next to each subprocess, object, etc., is a small command button that opens a form displaying the selected value in more detail. (Or use the double-click event procedure to do the same)

Hope I am getting warmer...
Richard

 

[SilentAiche]

Waymond,

Out of curiousity, what volume of records are you dealing with? How many rules, narratives, processes, etc.?

Also, how dynamic is the data? Will it change (either grow or shrink) often?

Access can handle the problem, as willir demonstrated, but it seems odd to have an "all text" database (I wanna crunch some numbers!!)

Of course, I'd be the first to admit my experience is limited, so maybe such db's are more common than I realize.

Actually, my first thought when I read your question was - [blue]Organizational Chart[/blue].

Good luck!
Tim