Relationships bwtween more than one table

f123 via AccessMonster.co · External Since: Oct 27, 2009 Posts: 2

am working now on one database where i have 5 tables, each record from one
table is related to one another in other table with One to Many Relation

in other words, each record from one table may has many records on the next
table

Example:

Tbl 1 ...Project
Tbl 2....Activity
Tbl 3...Description
....
....and so on

now Project May has Many Activity
the Activity May has Many Description
and the Description may has Many Records

Project
/ \

Activity1
Activity2
/ \ /
\
Description1 Description2 Description1
Description2
/ I \ / I \ /
I \ / I \
.

Jeff Boyce · External Since: Nov 04, 2004 Posts: 2342

Is there a question?

(note: your example has "Description" coming from Project ... contrary to
what you described)

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"f123 via AccessMonster.com" <u50920@uwe> wrote in message
news:9e39c0dd4167e@uwe...
> am working now on one database where i have 5 tables, each record from one
> table is related to one another in other table with One to Many Relation
>
> in other words, each record from one table may has many records on the
> next
> table
>
> Example:
>
> Tbl 1 ...Project
> Tbl 2....Activity
> Tbl 3...Description
> ...
> ...and so on
>
> now Project May has Many Activity
> the Activity May has Many Description
> and the Description may has Many Records
>
> Project
> /
> \
>
>
> Activity1
> Activity2
> / \
> /
> \
> Description1 Description2 Description1
> Description2
> / I \ / I \ /
> I \ / I \
> .

John W. Vinson · External Since: Jan 29, 2004 Posts: 2505

On Tue, 27 Oct 2009 16:23:41 GMT, "f123 via AccessMonster.com" <u50920@uwe>
wrote:

>am working now on one database where i have 5 tables, each record from one
>table is related to one another in other table with One to Many Relation
>
>in other words, each record from one table may has many records on the next
>table
>
>Example:
>
>Tbl 1 ...Project
>Tbl 2....Activity
>Tbl 3...Description
>...
>...and so on
>
>now Project May has Many Activity
>the Activity May has Many Description
>and the Description may has Many Records
>
> Project
> / \
>
>
> Activity1
>Activity2
> / \ /
>\
> Description1 Description2 Description1
>Description2
> / I \ / I \ /
>I \ / I \
> .

Ok; do you have a question?

The Description table should have a foreign key to the Activity table, and the
Activity table should have a foreign key to the Project table. Do you have
these? What specific problem are you having?
--

John W. Vinson [MVP]

f123 via AccessMonster.co · External Since: Oct 27, 2009 Posts: 2

Thanks for your reply, am sorry it seems that i forget to mention my probelm,

however, the problem is i dont know how to create the relationship between
each table

also, i need to see the best way to present all the tables on one form where
i can update all the data?

appreciate any help

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-tablesdbdesign/200910/1

John W. Vinson · External Since: Jan 29, 2004 Posts: 2505

On Tue, 27 Oct 2009 16:23:41 GMT, "f123 via AccessMonster.com" <u50920@uwe>
wrote:

>am working now on one database where i have 5 tables, each record from one
>table is related to one another in other table with One to Many Relation
>
>in other words, each record from one table may has many records on the next
>table
>
>Example:
>
>Tbl 1 ...Project
>Tbl 2....Activity
>Tbl 3...Description
>...
>...and so on
>
>now Project May has Many Activity
>the Activity May has Many Description
>and the Description may has Many Records

Tbl1 (let's call it Projects, meaningful object names are easier to
understand) should have a Primary Key field. Let's call it ProjectNo.

Tbl2 - Activities - should have a Primary Key, ActivityID; it should also have
a field ProjectNo, of the same datatype and size as the ProjectNo field in
Projects; this field is called a "foreign key".

Tbl3 - Description - should have a Primary Key, DescriptionID, and an
ActivityID field matching Activities.ActivityID. If Activities.ActivityID is
an Autonumber, then Description.ActivityID should be a Long Integer.

You would use the Relationships window to define a relationship from Projects
to Activities, on ProjectNo; and from Activites to Description, on ActivityID.

To see data from all three tables on a form, you can use a Form based on
Projects, with a Subform based on Activities (for simplicity this form should
be in Single Form view, not datasheet or continuous; on the subform you could
have a subform (a sub-subform to be precise), based on Description. This could
be a Continuous Form if you want to see multiple descriptions at once. It
would use ActivityID as its master/child link field.
--

John W. Vinson [MVP]