Darlene said:
I am creating a db and learning how to use and master this program in a
live
enviornment. Can someone please help me understand the differences and
uses
of Relationships between tables vs linking tables together. Does it
effect
the design of the table? Does it effect the quiery results?
Thank you for any assistance that can be offered.
In a relational database, entities (represented by rows in tables) are
related to others by data values that they hold in common. Relationships
define how tables are semantically related, by identifying the field or
fields in each table that ought to hold the same value(s) as the related
record in the other table. They also define whether there should be many
records in one table that are related to a single record in another table (a
many-to-one relationship), or whether there should be at most one matching
record in each table (a one-to-one relationship).
Access lets you define a relationship and have it be either enforced or
unenforced. If a relationship is not enforced, it really serves only as a
data model and as a to the query designer when you use it to construct
queries -- the query designer knows how to join the tables.
If a relationship is enforced, then the database engine ensures what is
called "referential integrity" -- no child record can exist without its
parent. It does this by either refusing to let you delete the parent record
or modify its linking key field, or -- if you have the Cascade Updates
and/or Cascase Deletes options set for the relationship -- automatically
updating or deleting the child records to match the change/deletion of the
parent record.
In a query involving multiple tables, you normally join the tables on the
fields that relate them. That's how you link the tables to bring related
information from each table into your query. If you have defined a
relationship between the tables, the query designer sets up a join
automatically, but you can join tables on fields for which you haven't
defined a relationship, and you can change the join that the query designer
sets up automatically. One of the most common changes you would make to the
join would be to change the type of join from an "inner join" to an "outer
join".
In an inner join, only records that have matching fields in both tables will
be returned by the query. In an outer join, you select one side or the
other to be the primary table, so that you get all the records from that
table and only the matching records from the other table. In the query
results, the fields that would have come from the secondary table are Null
when there is no matching record in that table. So, as you see, the
relationship itself doesn't affect the query results, but the way the join
is specified in the query does.
