If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Relationship vs linking tables
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. Darlene |
#2
|
|||
|
|||
Relationship vs linking tables
"Darlene" wrote in message
... 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. -- Dirk Goldgar, MS Access MVP www.datagnostics.com (please reply to the newsgroup) |
Thread Tools | |
Display Modes | |
|
|