creating relationships

[Ronnie]

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.

[Salad]

Ronnie wrote:

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.

I highly recommend you have an autonumber primary key for each table.
Your link between the two tables are the autonumbers; PersonalID is
primary key in Personal and OrgID in Organization.

You could have another table with PersonalID and OrgID...and any other
relevent info you need...like date joined, date terminated, date added
to the system, etc. When you add/edit a person, a subform could exist
where you add the organizations the person belongs to.

This way you can swing it so that you can view an organization and see
all assigned persons to it or visa versa, persons and all orgainzations
they belong to.

[Duane Phillips]

You have what is called a "many-to-many" relationship.
A person can belong to many Orgs, and a single Org can be linked to many
different persons.
You need another table to join the two, and should get rid of the 5 OrgID
fields in the Personal Table.
This allows you to have an unlimited number of relationships, or none at
all, and not have blank ID fields sitting in your tblPerson wasting space.
So your Person table might have:
PersonID <<< primary key for this table
FirstName
LastName
(etc.)

The new table might be called "tblPersonO rg" and have:
PersonOrgID <<< primary key for this table
PersonID <<< ID from tblPerson
OrgID <<< ID from tblOrg
Unless there is something more like Employee Start Date or some other field
about a person linking to an org, you shouldn't need any other fields in
this table.

Your Org table might have
OrgID <<< primary key for this table
OrgName
OrgInfo
(etc.)

HTH.
~ Duane.

"Ronnie" <ro*****@yahoo. com> wrote in message
news:a9******** *************** ***@posting.goo gle.com...

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.

[Bas Cost Budde]

Ronnie wrote:

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??

Not too harsh! But you missed one point. If a person can belong to more
organisations, and an organisation can 'contain' more than one person,
there is a n:m relationship (that is, many to many) that is mapped
through a third table. You don't have that one.

With your five fields, you would run into trouble if a sixth relation
had to be added.

The new table, let's call it Membership, should have the same fields as
the primary keys from Personal and Organization; all those fields are
the primary key in the Membership table.

Create two relations with referential integrity (and I usually set
cascaded updates on, not cascaded deletes), both from Personal and
Organization (drag from those) to Membership (drop here).

Now, for every old orgid1 you create a record in Membership; same for
orgid2 (excluding Nulls of course), etc.
After you've done that, the fields orgidX can be deleted.

--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
I prefer human mail above automated so in my address
replace the queue with a tea

[Laudi]

Creating different fields for each organization is the temptation when
first starting out. What you need is one field which will display which
orginization the person is connected. In the relationships window, link
the two tables with a "one-to-many". You said one person can belong to
many orginizations. By using an autonumber, as one person suggested, for
your primary key, you may have several lines for any individual, one line
for each orginization the person belongs to. Consider using a drop down
box for the orginization selection. If you think the list of orginizations
will change, use a seperate table to base the list on or base the list on
your orginization table.

Now, as you may not want to type all the personal information over and
over for each orginization the person belongs to, you will need a third
table. You have one table with all the personal information on each
person. If you make the PersonsName the key field, you will ensure you do
not get two people the same - like John Smith - or have double entries
for the same person. You have one table containing all the information on
each orginization. The third table yo need is just for the links,
PersonName and Orginization. The middle table creates a "many-to-many"
relationship, becuase you have one-to-many Person to Link and one-to-many
Organization to Link. Make the OrganizationNam e the key field in the
Organization table, so the Link table will have PersonsName listed beside
OrganizationNam e. You will have one line for each combo. In the Link
table. You link the one-to-many relationship from the key field to a non
key field, so PersonsName and OrginizationNam e cannot be key. Also, key
must be unique, so can not for that reason. You create an autonumber key
field in the Link table, because you are not worried about any duplicate
information there.
Hope this gives you a more clear picture.