Microsoft Office Tutorials and References
In Depth Information
What Are Relationships?
Primary key fields for your tables
A primary key field (or just key) is a field that uniquely identifies each record
in a table. If each product in a Products table has a different product code,
the ProductCode field uniquely identifies a record in this table. If you
search the Products table for a specific product code, you come up with —
at most — one record.
Not all tables have an obvious key field, however. You may have to combine
two or three fields to come up with values that are different for every record
in the table. In a Books table, for example, you may have several books with
the same title. If you assume that an author never writes more than one
book with the same title, a combination of the Title and Author fields may
work as a key field.
For an address list, you may think that the combination of first and last
names would do the trick, but it doesn’t take long before you realize that you
know two Jim Smiths. You could use a combination of first name, last name,
and phone number, but you have another, better alternative: Have Access
issue each record a unique number, and use that number as the key field. If
you can’t figure out a good set of keys to use for a table, add an AutoNumber
field; Access automatically numbers the records as you add them.
Access doesn’t absolutely require every table to have a primary key field
(or fields), but if you plan to set up relationships between your tables, some
tables definitely need a primary key field. Also, key fields speed a search for
records; Access creates an index for each primary key field and can zero
in on any record quickly by using those primary key values. When Access
offers to add a primary key for you, accept its offer!
What Are Relationships?
No trick question here; some projects ( most projects) require more than one
table. A database for a store, for example, has to handle lists of customers,
lists of products, and lists of vendors, just for a start. All those bits of data
have to be coordinated in some useful way.
Relational databases fill the bill. A relational database contains tables that are
related — well, no, not like cousins or sisters-in-law. Two tables are related if
they contain fields that match. If you have an online store, a relational
database system probably includes related Products and Vendors tables:
✦ The Products table: This table is a list of the products you sell, containing
one record for each product. Each record for a product includes a field
that identifies the vendor from which you buy your stock.
✦ The Vendors table: This table is a list that includes name, address, and
other information about each vendor.