Ways to get started if you've used other database or spreadsheet applications

If you are familiar with other database or spreadsheet applications, then you probably know the basics of how these applications work and what databases are used for. Microsoft Access differs from many other database applications by allowing you to create relational databases. Access also provides many options for working with other database programs such as Microsoft SQL Server.

Relationships in a database

After you've set up different tables for each subject in your Microsoft Access database, you need a way of telling Microsoft Access how to bring that information back together again. The first step in this process is to define relationships between your tables. After you've done that, you can create queries, forms, and reports to display information from several tables at once. For example, this form includes information from four tables:

Orders form displaying related information from five tables at once

1  The Customers table

2  The Orders table

3  The Products table

4  The Order Details table

How relationships work

In the previous example, the fields in four tables must be coordinated so that they show information about the same order. This coordination is accomplished with relationships between tables. A relationship works by matching data in key fields — usually a field with the same name in both tables. In most cases, these matching fields are the primary key from one table, which provides a unique identifier for each record, and a foreign key in the other table. For example, employees can be associated with orders they're responsible for by creating a relationship between the EmployeeID fields.

EmployeeID used as primary key in Employees table and foreign key in Orders table.

1  EmployeeID appears in both tables—-as a primary key ...

2  ... and as a foreign key.

A one-to-many relationship

A one-to-many relationship is the most common type of relationship. In a one-to-many relationship, a record in Table A can have many matching records in Table B, but a record in Table B has only one matching record in Table A.

A record in Suppliers table with more than one related record in Products table

1  One supplier ...

2  ... can supply more than one product ...

3  ... but each product has only one supplier.

A many-to-many relationship

In a many-to-many relationship, a record in Table A can have many matching records in Table B, and a record in Table B can have many matching records in Table A. This type of relationship is only possible by defining a third table (called a junction table) whose primary key consists of two fields — the foreign keys from both Tables A and B. A many-to-many relationship is really two one-to-many relationships with a third table. For example, the Orders table and the Products table have a many-to-many relationship that's defined by creating two one-to-many relationships to the Order Details table. One order can have many products, and each product can appear on many orders.

Foreign keys in Order Details table and matching primary keys in Orders table and Products table

1  Primary key from the Orders table

2  Primary key from the Products table

3  One order can have many products ...

4  ... and each product can appear on many orders.

A one-to-one relationship

In a one-to-one relationship, each record in Table A can have only one matching record in Table B, and each record in Table B can have only one matching record in Table A. This type of relationship is not common, because most information related in this way would be in one table. You might use a one-to-one relationship to divide a table with many fields, to isolate part of a table for security reasons, or to store information that applies only to a subset of the main table. For example, you might want to create a table to track employees participating in a fundraising soccer game. Each soccer player in the Soccer Players table has one matching record in the Employees table.

Every record in the Employees table can have only one matching record in the Soccer Players table.

1  Each soccer player has one matching record in the Employees table.

2  This set of values is a subset of the EmployeeID field and the Employees table.

About defining relationships

The kind of relationship that Microsoft Access creates depends on how the related fields are defined:

You can also create a relationship between a table and itself. This is useful in situations where you need to perform a Lookup within the same table. In the Employees table, for example, you can define a relationship between the EmployeeID and ReportsTo fields, so that the ReportsTo field can display employee data from a matching EmployeeID.

Note   If you drag a field that isn't a primary key and doesn't have a unique index to another field that isn't a primary key and doesn't have a unique index, an indeterminate relationship is created. In queries containing tables with an indeterminate relationship, Microsoft Access displays a default join line between the tables, but referential integrity won't be enforced, and there's no guarantee that records are unique in either table.

Referential integrity

Referential integrity is a system of rules that Microsoft Access uses to ensure that relationships between records in related tables are valid, and that you don't accidentally delete or change related data. You can set referential integrity when all of the following conditions are met:

The following rules apply when you use referential integrity:

Cascading updates and deletes

For relationships in which referential integrity is enforced, you can specify whether you want Microsoft Access to automatically cascade update and cascade delete related records. If you set these options, delete and update operations that would normally be prevented by referential integrity rules are allowed. When you delete records or change primary key values in a primary table, Microsoft Access makes necessary changes to related tables to preserve referential integrity.

If you select the Cascade Update Related Fields check box when defining a relationship, any time you change the primary key of a record in the primary table, Microsoft Access automatically updates the primary key to the new value in all related records. For example, if you change a customer's ID in the Customers table, the CustomerID field in the Orders table is automatically updated for every one of that customer's orders so that the relationship isn't broken. Microsoft Access cascades updates without displaying any message.

Note   If the primary key in the primary table is an AutoNumber field, setting the Cascade Update Related Fields check box will have no effect, because you can't change the value in an AutoNumber field.

If you select the Cascade Delete Related Records check box when defining a relationship, any time you delete records in the primary table, Microsoft Access automatically deletes related records in the related table. For example, if you delete a customer record from the Customers table, all the customer's orders are automatically deleted from the Orders table (this includes records in the Order Details table related to the Orders records). When you delete records from a form or datasheet with the Cascade Delete Related Records check box selected, Microsoft Access warns you that related records may also be deleted. However, when you delete records using a delete query, Microsoft Access automatically deletes the records in related tables without displaying a warning.

 

Working with other applications

Microsoft Access 2002 provides features for working with other programs.