relating tables

  • Thread starter Thread starter Lani
  • Start date Start date
L

Lani

I have a database of clients' personal financial
information, divided into tables (assets, liabilities,
real estate owned, etc.) Each table is related by the
same primary key: Client ID. When defining this
relationship, do I establish a one-to-one relationship or
many-to-many relationship? How?

Thanks.
 
If it were me...
ClientInformation in one table joined one-to-many with
assets, liabilities, real estate tables on the many side.

Hope that helps...
 
I have a database of clients' personal financial
information, divided into tables (assets, liabilities,
real estate owned, etc.) Each table is related by the
same primary key: Client ID. When defining this
relationship, do I establish a one-to-one relationship or
many-to-many relationship? How?

Thanks.
Click to expand...

Unless each person has only one asset, or only one liability, or only
one property, then you would have three one-to-many relationships.
Even if most people own only one lot of real estate, you'ld still use
a one to many because a client *could* own two or more!

To create a one to many relationship, you should have a Clients table
with the ClientID as the primary key, containing biographical
information, contact address, and so on; each other table should have
SOME OTHER FIELD as the Primary Key, and a separate ClientID field as
a "foreign key". You should use the Relationships window to join the
ClientID primary key to the Assets ClientID foreign key field, and the
same for the other tables.

The ClientID should *not* be the primary key of the Assets table
because a Primary Key is by definition unique within the table; making
it the PK would require that a given client may posess one and only
one Asset. Some of your clients might be a bit perturbed at such a
restriction (though being limited to only one liability has its
attractive points!)
 
Ideally relationships should be one : many, Access doesn't tend to like many : many relationships and if you have any you may find that query results are unreliable

hope this help

----- Lani wrote: ----

I have a database of clients' personal financial
information, divided into tables (assets, liabilities,
real estate owned, etc.) Each table is related by the
same primary key: Client ID. When defining this
relationship, do I establish a one-to-one relationship or
many-to-many relationship? How

Thanks.
 
Ideally relationships should be one : many, Access doesn't tend to like many : many relationships and if you have any you may find that query results are unreliable.
Click to expand...

ummm... Many to Many relationships are almost universal; almost any
significant database will have one or more. Access likes them just
fine; but you must implement them correctly.

To model a many to many relationship, you must "decompose" it into two
one-to-many relationships by adding a new Table.

The classic example is the many to many relationship between Students
and Classes. To model this relationship you create an Enrollment
table, with fields for StudentID (one to many relationship to
Students) and for ClassID (one to many relationship to Classes).

The results of such a query are perfectly reliable, and such a
construction is completely routine in Access (or any relational
database).
 
Back
Top