In This Chapter
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.
Usually the primary key is a single field. In very special circumstances, two or more fields can share the job. The technical term for this type of key is a multifield key.
Uses
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 Customer table typically contains a Customer Number field. This field is the primary key. If your Customer table contains a dozen Jane Smiths, you need a way to tell them apart. The Customer Number field for each record uniquely identifies each Jane Smith — and every other customer, too.
Tables, by default, are sorted by primary key.
A primary key helps Access find a particular record much faster.
Your database could freak out if you don’t have a primary key.
Without a primary key, finding the requested records can be difficult for Access. Think of the Jane Smith example just used. How does Access know which Jane Smith you want if multiple Jane Smith customers are in your database? Well, by the primary key. It is unique for each customer and therefore can be used to uniquely identify each Jane Smith. Problem solved!
Rules
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).
Defaults: Access tries to save you time and trouble with the default actions it gives to the primary key:
Restrictions: You can’t just create primary keys willy-nilly. Access imposes these limits:
1. 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.
2. 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.
3. On the Ribbon, click the Primary Key button (shown in Figure 4-1).
A key symbol appears on the button next to the field name you selected.
The primary key is set!
Primary key values must be unique for each record. Leaving a primary key field blank is not an option. So start by looking at some sample data that will go into your table. Is there something like a phone number or customer number that will be unique for each record? If so, you’ve just found your primary key field. If not, then an AutoNumber field is the way to go. When you designate a field’s data type as AutoNumber, Access assigns sequential numbers to each record entered into the table. (Isn’t our good pal Access a peach?)
AutoNumber fields always create a unique identifier for each record. When you delete a record with an AutoNumber field, Access even keeps track of those numbers and will not use them again. (Access, you are quite a pal indeed!)
- 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.
The Primary Key to Success
A table’s primary key is a special field in your table. You use this field touniquely identify each record in the table.Usually the primary key is a single field. In very special circumstances, two or more fields can share the job. The technical term for this type of key is a multifield key.
The lowdown on primary keys
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.Uses
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 Customer table typically contains a Customer Number field. This field is the primary key. If your Customer table contains a dozen Jane Smiths, you need a way to tell them apart. The Customer Number field for each record uniquely identifies each Jane Smith — and every other customer, too.
Tables, by default, are sorted by primary key.
A primary key helps Access find a particular record much faster.
Your database could freak out if you don’t have a primary key.
Without a primary key, finding the requested records can be difficult for Access. Think of the Jane Smith example just used. How does Access know which Jane Smith you want if multiple Jane Smith customers are in your database? Well, by the primary key. It is unique for each customer and therefore can be used to uniquely identify each Jane Smith. Problem solved!
Rules
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).
Defaults: Access tries to save you time and trouble with the default actions it gives to the primary key:
- Access really, really wants you to have a primary key in your table.
- If you create a new table in table design mode without a primary key, Access suggests adding a primary key field when you save the table.
- Access gives this automatic primary key field a wildly creative name — ID — with an AutoNumber data type.
- If the first field you add in a table is an AutoNumber type, Access automatically makes that AutoNumber field the primary key.
- Access indexes the primary key field automatically.
Restrictions: You can’t just create primary keys willy-nilly. Access imposes these limits:
- A table can have only one primary key.
- You can’t use the Calculated, Attachment, Memo, Hyperlink, and OLE Object data types 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.
- All primary key indexes must have a name (just as all fields must have a name).
- Access automatically names all primary key indexes PrimaryKey.
Creating a primary key
To create a primary key, follow these steps:1. 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.
2. 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.
3. On the Ribbon, click the Primary Key button (shown in Figure 4-1).
A key symbol appears on the button next to the field name you selected.
The primary key is set!
 |
| Figure 4-1: The completed primary key. |
The key to table happiness
What makes a good key field? How do you find the right one? Good questions! In fact, they’re the two most important questions to ask about a primary key.Primary key values must be unique for each record. Leaving a primary key field blank is not an option. So start by looking at some sample data that will go into your table. Is there something like a phone number or customer number that will be unique for each record? If so, you’ve just found your primary key field. If not, then an AutoNumber field is the way to go. When you designate a field’s data type as AutoNumber, Access assigns sequential numbers to each record entered into the table. (Isn’t our good pal Access a peach?)
AutoNumber fields always create a unique identifier for each record. When you delete a record with an AutoNumber field, Access even keeps track of those numbers and will not use them again. (Access, you are quite a pal indeed!)
Comments
Post a Comment