If you look in the Wedding List sample database (WeddingList.accdb) included on the companion CD, you’ll find it very simple, with one main table and a few supporting tables for data such as titles, cities, and groups. Most databases are usually quite a bit more complex. For example, the Housing Reservations sample database contains six main tables, and the Conrad Systems Contacts sample database contains more than a dozen tables. If you had to create every table "by hand," it could be quite a tedious process.
Fortunately, Access 2007 comes with table templates to help you build a few common tables. Let’s move on to a more complex task—building tables like those you find in Conrad Systems Contacts. Click the Microsoft Office Button and then click New. This returns you to the Getting Started screen, ready to define a new blank database. For this exercise, create a new blank database and give it the name Contact Tracking.
To build a table using one of the table templates, close the table that Access 2007 created when you opened the database (Table1), click the Create tab on the Ribbon, and then click the Table Templates button in the Tables group. Access displays a list of five table templates—Contacts, Tasks, Issues, Events, and Assets, as shown in Figure 27-12. Microsoft uses the term Quick Create to refer to this one-click table creation feature.
The five table templates, which represent some of the more common types of table structures found in databases, are as follows:
Contacts. Use this table template when you need to track your personal or business contacts. Key fields in this template include the contact’s company, job title, and phone numbers.
Tasks. Use this table template for keeping track of various tasks and projects needing completion. Key fields in this template include start and due dates for the task and percentage complete.
Issues. Use this table template for recording various personal or business issues. Some key fields in this template include the title of the issue and the issue status.
Events. Use this table template as a personal organizer of your appointments. This template includes fields for start and end times of the event, the event date, and even the location.
Assets. Use this table template for keeping track of your assets. Key fields in this template include the acquisition date, the original price of the asset, and the current price.
Click Contacts in the Table Templates list, and Access 2007 builds a complete table structure for a contacts table, as shown in Figure 27-13. Access creates a total of 18 fields to identify the data elements for this contacts table. Use the horizontal scroll bar or press Tab to see the field names to the right. This contacts table template includes fields such as Company, First Name, Last Name, E-mail Address, Job Title, and so on to identify a single subject—a contact. The Table Templates command also automatically defines a data type for each of these fields.
Note
See Table 27-1 for a full discussion of the various data types available within Access 2007.
Table 27-1. Access Data Types
Data Type | Usage | Size |
|---|---|---|
Text | Alphanumeric data | Up to 255 characters |
Memo | Alphanumeric data—sentences and paragraphs | Up to about 1 gigabyte (but controls to display a memo are limited to the first 64,000 characters) |
Number | Numeric data | 1, 2, 4, 8 or 16 bytes |
Date/Time | Dates and times | 8 bytes |
Currency | Monetary data, stored with 4 decimal places of precision | 8 bytes |
AutoNumber | Unique value generated by Access for each new record | 4 bytes (16 bytes for ReplicationID) |
Yes/No | Boolean (true/false) data; Access stores the numeric value zero (0) for false, and minus one (−1) for true | 1 bit |
OLE Object | Pictures, graphs, or other ActiveX objects from another Windows-based application | Up to about 2 gigabytes |
Hyperlink | A link "address" to a document or file on the World Wide Web, on an intranet, on a local area network (LAN), or on your local computer | Up to 8,192 characters (each part of a Hyperlink data type can contain up to 2,048 characters) |
Attachment | You can attach files such as pictures, documents, spreadsheets, or charts; each Attachment field can contain an unlimited number of attachments per record, up to the storage limit of the size of a database file | Up to about 2 gigabytes |
Lookup Wizard | The Lookup Wizard entry in the Data Type column in Design view is not actually a data type. When you choose this entry, a wizard starts to help you define either a simple or complex lookup field. A simple lookup field uses the contents of another table or a value list to validate the contents of a single value per row. A complex lookup field allows you to store multiple values of the same data type in each row. | Dependent on the data type of the lookup field |
By default, Access 2007 assigned the name ID to the first field in this Contacts table. This field name is not very descriptive, so we will rename this field ContactID. There are several ways to rename a field using Access 2007, but for now we will focus on one of the easiest methods—renaming the field directly from Datasheet view. Double-click the heading of the ID field and then type ContactID, as shown in Figure 27-14. After you press Enter, Access immediately renames the field. Save this table now by clicking the Save button on the Quick Access Toolbar and name the table Contacts.
Figure 27-14. You can double-click a column heading in table Design view to change the name of the field.
You will further change this Contacts table later in this chapter so that it is more like the final tblContacts table in the Conrad Systems Contacts database. For now, close the Table window so that you can continue building other tables you need.