A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Relationships bwtween more than one table



 
 
Thread Tools Display Modes
  #1  
Old October 27th, 2009, 05:23 PM posted to microsoft.public.access.tablesdbdesign
f123 via AccessMonster.com
external usenet poster
 
Posts: 10
Default Relationships bwtween more than one table

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 \
  #2  
Old October 27th, 2009, 07:16 PM posted to microsoft.public.access.tablesdbdesign
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default Relationships bwtween more than one table

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 \
.



  #3  
Old October 30th, 2009, 09:30 PM posted to microsoft.public.access.tablesdbdesign
f123 via AccessMonster.com
external usenet poster
 
Posts: 10
Default Relationships bwtween more than one table

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/For...esign/200910/1

  #4  
Old October 31st, 2009, 01:51 AM posted to microsoft.public.access.tablesdbdesign
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Relationships bwtween more than one table

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]
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 03:45 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.