Relationship Question

[ronnroz]

Ok let me just say first that I am a newbie in Access and I don't know much of SQL or VB programming.

But I am trying to create this contact database using Access 97. I have created 2 tables, one Personal (it has all the personal information) and the other one Organization (this one has details of diff organizations). One person can belong to more than any (upto 5 in this case) organizations. So I included orgid1, orgid2, orgid3, orgid4 and orgid5 in Personal table. In Organization table, I have OrgId and Org name and other info.

How do I create the relationship, so when I am retrieving the information (creating a report), I can list the name of the person and all the organizations that person belong to.

Did I create the right tables, right structures? or is it all screwed up??

Please help, thank you in advance.

 

[PHV]

Get rid of the orgid1, ..., orgid5 fields in the personal table and create a third table to hold the many-to-many relation, ie with PersonID and OrgId each as Foreign Key referencing the corresponding table and with a composite Primary Key made on this two fields.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[lespaul]

First, read 'The Fundamentals of Relational Database Design'.

Then you are going to create a third table, with two fields:

tblPersonOrganizations
PersonID
OrganizationID

with a structure like this each person can have UNLIMITED number of organizations without changing the table structure. You mention that you have Org1, Org2, etc. in the Person table, but what happens when a person has a 6th or 7th organization? I know, they always say, "That will NEVER happen!" THEY LIE!!! IT WILL ALWAYS HAPPEN!! And with your current structure you would have to add a new field to the Person table. With the tblPersonOrganizations table, that situation doesn't occur.

HTH

leslie