Chapter 5
In This Chapter
Identifying your records uniquely with a primary key
Understanding relationships
Building relationships between your tables
Indexing for faster queries
Life in today’s world is all about doing things faster and more efficiently to increase productivity. Isn’t that what your life is about? Oh, you have a life outside the office, too? (What a concept.) Maybe you have a relationship? This chapter is about making your databases faster and about building good relationships (the database kind, not the human kind!).
As with any good relationship, the end result is often harmony and happiness. Making your Access tables work well together will make things so much easier in so many ways as you move on to build your queries, forms, and reports. The good news is that building relationships in Access takes a lot less time than building human relationships.
How can you make Access work faster and more efficiently? With key fields and indexes, that’s how! Each table should have that one special field assigned as a primary key. A primary key prevents duplicate records from being entered into a table — hence more efficient data entry. (I love the word hence!) To retrieve your data faster, you need to create the proper balance of indexes for each table. Not enough indexes, and querying 100,000 records will take forever; too many, and the same could be true. So assigning indexes to the correct fields is an art form. You find out all about the art of indexes in this chapter.
A table’s primary key is a special field in your table. You use this field to uniquely identify each record in the table.
Before we discuss how to create a primary key, you’ll need to know some rules and guidelines for using one. This section contains the when, where, and why of the primary key.
Almost every table you create needs a primary key. Here’s why:
A primary key organizes your data by uniquely identifying each record.
That’s one reason why a primary key makes your database work a little faster. For an explanation of indexes and their creation, see the section “Indexing for Faster Queries,” later in this chapter.
For example, a Volunteer table typically contains a Volunteer Number
field. This field is the primary key. If your Volunteer table contains a dozen Jane Smiths, you need a way to tell them apart. The VolunteerNumber
field for each record uniquely identifies each Jane Smith — and every other volunteer, too.
Tables, by default, are sorted by primary key.
A primary key helps Access find a particular record much faster.
Before you create a primary key, you need to know a few guidelines. Here’s a handy listing:
Location: Access doesn’t care where the primary key field appears in the table design. The key can be the first field, the last field, or buried in the middle.
Even if Access doesn’t care where you put things, I always recommend that you make the primary key field the first field in your table. It makes relationships easier to build (as you see later in this chapter).
Access gives this automatic primary key field a wildly creative name — ID
— with an AutoNumber
data type.
AutoNumber
type, Access automatically makes that AutoNumber
field the primary key.Restrictions: You can’t just create primary keys willy-nilly. Access imposes these limits:
You can’t use the Calculated
, Attachment
, and OLE Object
data types or a multivalued field for a primary key.
Avoid using the yes/no
field type in a primary key. You can have only two records in such a table: Yes
and No
.
PrimaryKey
.To create a primary key, follow these steps:
Open the table in Design view.
If you just asked yourself “how do I do that?” then it might not be time for you to create a primary key. Chapter 3 shows you the table basics you need before you can create a primary key.
Click the field name for the primary key.
Don’t know which field to select for your primary key? See the sidebar, “The key to table happiness.” The preceding section, “Rules,” relates the guidelines for selecting a primary key.
On the Ribbon, click the Primary Key button (shown in Figure 5-1 ).
A key symbol appears on the button next to the field name you selected.
The primary key is set!
Relational databases split data among two or more tables. Access uses a linking field, called a foreign key, to tie related tables together. For example, one table may contain volunteer names and addresses, whereas another table tracks the volunteer event participation history. The event participation information is tied to the volunteer information with a linking field, which (in this example) is probably a Volunteer ID.
Why is this important? Well, suppose you need to print an invoice schedule for volunteer Anita Cash. By placing the Volunteer ID in the Events table and relating the Events table to the Volunteer table via the Volunteer ID, you can pull Anita Cash’s name and address information for the schedule without having to put that information in the Events table.
Keep this in mind when relating tables:
Tables you want to relate must have at least one field in common. Although the field name need not be identical, its data type must be the same in each table. For example, you can’t relate a text field to a number field.
Keep key field names consistent between your tables. If you don’t, that ball of confusion will roll your way at some point down the road.
There is more than one type of table relationship in Access. When you relate two tables, you can choose one of three possible relationship types.
One-to-many relationships connect one record in the first table to many records in the second table. This is the default relationship type.
Typical example: One volunteer may participate in many events, so one volunteer record is linked to many event records in the Events table.
One-to-one relationships link one record in the first table to exactly one record in the second table.
Many-to-many relationships link many records in one table to many records in another table.
Every individual product needs to be available to every customer.
In other words, many customers need to be able to order many of the same products. The database needs to satisfy queries that look for both of these:
In Access 2016, you can link many customers to many of the same products two ways: multivalued fields and junction tables.
Access 2016 allows the creation of many-to-many relationships between two tables via multivalued fields.
A multivalued field can store many similar data items. Adding a multivalued field ends the need for creating multiple records to record multiple products ordered on one customer order. For example, you can add a multivalued field called ProductID
to the Order Detail table. All products ordered can be stored in one field, so only one record per order is required.
A junction table is a special table that keeps track of related records in two other tables:
For example, a junction table called Orders can connect the customers to the order details for a particular order. The junction table has a one-to-many relationship with both the Customer and Order Details tables.
If you can drag-and-drop, you can build a table relationship.
When you’re ready to play the matchmaker between your loving tables, here’s how to do it.
To build a table relationship, first open the Relationships window. Follow these steps:
Click the Database Tools tab on the Ribbon.
The Relationships group appears on the Ribbon. (See Figure 5-2.)
From the Relationships group, click the Relationships button.
The Relationships window appears. The first time you set a relationship, the Show Table dialog box appears as well. More on that in the next section.
If some tables are already listed in the window, someone (or some wizard) has already defined relationships for this database. If you’re not sure how they got there and if more than one person is working on your database, stop and consult all database developers before changing the relationships. What might work for you could be disastrous for your colleagues.
When the Relationships window is open, you can select and relate tables.
For each pair of tables you relate, you must select the tables and then join their common fields. The following sections show you how.
To select tables to relate, open the Relationships window (as described in the preceding section) and follow these steps:
Choose Show Table from the Ribbon’s Relationships group. (If you don’t see the Relationships group, select the Database Tools tab on the Ribbon.)
The Show Table dialog box appears, listing the tables in the current database file.
For each pair of tables you want in the relationship, follow these steps:
Click Add.
In the large Relationships workspace, a little window lists the fields in the selected table. As you add tables to the layout, a separate window appears for each table. You can see these windows below the Show Table dialog box in Figure 5-3.
Repeat Step 2 for each pair of tables you want to relate. If one of the tables in the pair is already present (because of an existing relationship it has with another table), you don’t have to add it again.
When you have all the tables present, you’re ready for these tables to get to know each other. The following sections show how to relate the tables.
This section contains all the information you’ll need to create, edit, and delete your table relationships.
After you select the tables (as shown in the preceding instructions), follow these steps to create a relationship between two tables:
Decide which two tables you want to relate.
Because the one-to-many relationship is the most common, these instructions pertain to it. The two tables in a one-to-many relationship are designated as fulfilling one of two roles:
To make relating tables easier, put related fields near the beginning of the field list. In Access, you must see the related fields on the screen before you can make a relationship. If the related fields are not at the beginning of the field list, you have to do a lot of scrolling to find them. To move a field, open the problem table in DesignView, point to the button that’s left of the field name, then press and drag on the button to move the field up.
Put the mouse pointer on the field you want to relate in the parent table.
Usually the field you want to relate in the parent table is the primary key.
While holding down the left mouse button, follow these steps to join the parent field to the child field:
Drag the mouse pointer from the parent field to the child table.
A plus sign appears at the base of the mouse pointer.
Release the mouse button.
The Edit Relationships dialog box appears, detailing the soon-to-be relationship, as shown in Figure 5-4.
Be very careful before releasing the mouse button. Put the tip of the mouse pointer directly on the child field before you let go.
Double-check that your field names are the correct ones and then click Create.
Access illustrates the new relationship in the Relationships window:
To relate another pair of selected tables, repeat Steps 1 through 5.
After you relate tables, you can see, organize, and remove the relationships.
Click the Relationship line connecting the two tables.
If you are successful, the line will thicken. That means the line is selected.
Tap the Delete key on your keyboard and then Yes in the resulting message box.
Voilà! The relationship is gone.
If you’re relating many tables together, the Relationships window may look a little messy because Relationship lines will cross each other. This makes it difficult to determine which tables are related to each other. To rectify this situation, click and drag the title bar of a table window to another part of the screen. It’s good practice — although not always possible — to show parents either above or to the left of their children. Try to arrange the parent and child tables so the lines between the parent and child tables don’t cross over any lines that illustrate other table relationships.
You may find yourself sitting for a minute or two waiting for a query or report to run. (I’ve sat longer than that for some reports during the development stage.) So what can be done to speed up your queries? Add indexes to your tables, that’s what.
Indexes dramatically speed up queries and sorts. When you sort or query a table on an indexed field, the index has already done most of the work.
The benefit of an index depends on the number of records in the table:
Here’s the lowdown on indexes:
Here are some guidelines to help you decide which fields to index, presented in their recommended order:
Start by querying the table whose query performance you’d like to improve (for more on queries, see Chapter 13).
Make note of the time it took to run the query.
Next, index each field that you know you’ll query frequently.
For example, in a Volunteer table, you might query by Volunteer ID, Volunteer Last Name, and Volunteer State.
Finally, query the newly indexed table.
If the query time improves, your indexes are correct. If your query time worsens, try removing one index at a time, starting with the field you think you’ll query the least. Rerun your query and note performance.
When you’ve optimized performance, your indexing is complete.
Apply an index type.
The sidebar “To duplicate or not to duplicate” shows which index type to apply.
To list a table’s indexes, follow these steps:
After you decide on the correct fields to index (as outlined previously), creating an index is a snap.
To add or remove field indexes, open the table in Design view and follow these steps:
Click the name of the field you want to index.
The blinking cursor lands in the field name.
In the General tab of the Field Properties section, click the Indexed box.
Now the cursor moves into the Indexed box, and a down arrow appears on the right end of the box.
If the Indexed property is unavailable, this field type doesn’t work with indexes. You can’t index Calculated
, Attachment
, OLE Object
, or multivalued
fields.
Click the down arrow at the end of the Indexed box.
A list of index options appears:
The previous sidebar “To duplicate or not to duplicate” shows how to determine the correct index setting.
Select the index type you want from the list.
To remove an existing index from the selected field, click No.
To make the change permanent, click the Save button on the Quick Access Toolbar.
If your table contains thousands of records, Access may take a few moments to create the index.