reasons to link tables

I understand the importance if identifying all table relationships during
the database design phase for the purpose of good design, but what is the
purpose of linking tables with the relationship tool? Is there more than
database integrity? For example, do these relationship links affect either
the design view and/or use of queries, forms, and reports?

Best,
Christopher

Creating a relationship will add indexes for the foreign keys, if you
haven't already.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"Christopher Glaeser" wrote in message
...
I understand the importance if identifying all table relationships during
the database design phase for the purpose of good design, but what is the
purpose of linking tables with the relationship tool? Is there more than
database integrity? For example, do these relationship links affect
either
the design view and/or use of queries, forms, and reports?

Best,
Christopher

Creating a relationship will add indexes for the foreign keys, if you
haven't already.

Thanks! I've watched the lynda.com Access tutorials several times regarding
table relationships, and the part I'm still missing is the direction of the
arrow head and the many-to-one vs one-to-many. How does the relationship
builder know the link is one-to-one, many-to-one, or one-to-many? Do the
attributes of the table fields affect this?

Best,
Christopher

On Fri, 22 Oct 2004 13:49:52 -0700, "Christopher Glaeser"
wrote:

I understand the importance if identifying all table relationships during
the database design phase for the purpose of good design, but what is the
purpose of linking tables with the relationship tool? Is there more than
database integrity? For example, do these relationship links affect either
the design view and/or use of queries, forms, and reports?

The data integrity bit is quite enough reason to link the tables, with
relational integrity enforced; doing so protects you from entering
"orphan" records, misspelled names, duplicate data, etc.

But yes, if you have established a Relationship between two tables,
creating a Query involving those tables will automatically link them
in accord with the relationship. You can change the Join if you need
to, but it's a handy default. In addition, if you create a Form with a
Subform, Access will automatically fill in the linking fields based on
the relationship; likewise for Reports and Subreports.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

On Fri, 22 Oct 2004 14:51:46 -0700, "Christopher Glaeser"
wrote:

How does the relationship
builder know the link is one-to-one, many-to-one, or one-to-many?

If the field that is involved in the linkage, in either table, has a
unique Index on it - such as a Primary Key index - that table will be
on the "one" side of the relationship. If you link from Primary Key to
Primary Key you'll get a one-to-one relationship; this is uncommon and
may not be what you want, but there are special cases (Subclassing for
example) where it's legitimate.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

Here's an example of a 1-to-many relationship. Let's say you have a list of
customers, and you want each customer to have several addresses. You would
put the customers in 1 table, and their addresses in another. Simply by
defining, let's say, CustomerID, as "indexed/dupes allowed" in the table
with the addresses, you are setting it up to be used as a foreign key on the
many side of a 1-to-many relationship. This table will be on the "many"
side of ANY relationship it's involved in where CustomerID is used as the
related (or foreign) key. When you're in the relationships window drawing
a link between these 2 tables, the actual type of relationship is not really
defined at this point by you, rather, it's understood by Access based on how
you've already defined these indexes when you made (or last edited) the
table, and, significantly, also by the actual contents of the tables. For
example, if 2 records in the addresses table have the same CustomerID,
Access will *assume* a 1-to-many relationship. If you had intended this to
be a 1-to-1 relationship, you have to FIRST delete other records in the
addresses table for that customer, then define CustomerID as "indexed/dupes
not allowed", OR keep the addresses and understand that the relationship is
1-to-many, which might not have been obvious at first (or, as the case may
be).

In a 1-to-1 relationship, there would only be 1 address per customer, in
which case CustomerID in the addresses table would also be primary (i.e,
dupes not allowed).

"Many-to-1" and "1-to-Many" are the same construction. To describe a
relationship as one or the other is a matter of perspective.


"Christopher Glaeser" wrote in message
...
Creating a relationship will add indexes for the foreign keys, if you
haven't already.

Thanks! I've watched the lynda.com Access tutorials several times
regarding
table relationships, and the part I'm still missing is the direction of
the
arrow head and the many-to-one vs one-to-many. How does the relationship
builder know the link is one-to-one, many-to-one, or one-to-many? Do the
attributes of the table fields affect this?

Best,
Christopher

Let me approach your question from a conceptual rather than technical
direction. Others here are far more skilled than I at the technical details
anyhow. Ask yourself plain language questions. Is each customer unique?
Can each customer be associated with several orders? Can an order be
associated with several customers? If the answers are Yes, Yes, and No, the
primary key field from the customer table will have a corresponding foreign
key field in the orders table. Drag one field onto the other in the
relationship window and in many cases the details will resolve themselves.
If each order can contain several items, the PK from the orders table has a
corresponding foreign key field in the OrderDetails table. The PK is the One
side of the relationship, and the FK is the Many side of the relationship.
Access will not negotiate this point.

If each employee can attend many meetings, and each meeting can have many
attendees, a third table is needed to resolve the many-to-many relationship.
The third table needs to contain FKs corresponding to the PK from the
Employee table and the PK from the Meetings table.

"Christopher Glaeser" wrote:

Creating a relationship will add indexes for the foreign keys, if you
haven't already.

Thanks! I've watched the lynda.com Access tutorials several times regarding
table relationships, and the part I'm still missing is the direction of the
arrow head and the many-to-one vs one-to-many. How does the relationship
builder know the link is one-to-one, many-to-one, or one-to-many? Do the
attributes of the table fields affect this?

Best,
Christopher