Creating a Table Manually
In the previous exercise, you created a contact management database application based on an Access 2007 template. The database had all the tables, forms, reports, and code needed to import, store, and use basic information about people. But suppose you need to store different types of information for different types of contacts. For example, you might want to maintain different types of information about employees, customers, and suppliers. In addition to the standard informationsuch as names, addresses, and phone numbersyou might want to track these other kinds of information:
Employee Social Security numbers, dates of hire, marital status, deductions, and pay rates
Customer orders and account status
Supplier contacts, current order status, and discounts
You could start with the template, add a lot of extra fields to the Contacts table, and then fill in just the ones you want for each contact type; but cramming all this information into one table would soon get pretty messy. In this instance, it's better to manually create a database that includes one table for each contact type: employee, customer, and supplier.
With most computer programs, it is important to save your work frequently to avoid losing it if your computer crashes or the power goes out. With Access, it is not only not important to save your data, it is not possible to manually save it. When you move the insertion point out of a record after entering or editing information, Access saves that record. This means that you don't have to worry about losing your changes, but you do have to remember that most data entry changes you make are permanent and can be undone only by editing the record again.
Note, however, that changes to properties and layout are not saved automatically. If you create a new table, form, or report, or modify the properties or layout of an existing one, you will be prompted to save the changes before closing the object or the database.
For information about ways of controlling table content, see Chapter 7, "Keeping Your Information Accurate."
In this exercise, you will open a blank database, create a table, manually add a record, and import some records. There are no practice files for this exercise.
BE SURE TO start Access before beginning this exercise.
On the Getting Started with Microsoft Access page, under New Blank Database, click Blank Database.
Access displays information about the selected template on the right side of the program window.
In the File Name
box, type 02_Manual
, click the Browse for a location
button and browse to the Documents\MSP\SBS_Access2007\Chapter02
folder. Then click OK
Browse for a location
You can't create a blank database without saving it. If you don't provide a path and file name in the File Name box, Access saves the file in a default location and with a sequentially-numbered default name. The usual location is in the Documents folder, and the name is in the format Database1.accdb.
to create the blank database in the specified location.
The database opens, displaying a new blank table named Table1, in a group named Table1.
Notice that the first column is titled ID and the second is titled Add New Field. Access automatically creates the ID fieldyou can delete it if you don't need it. The ability to add fields to a table by simply typing data in the first row is new with Access 2007. As you enter information in the cells, Access adds fields to the table and guesses at the data type and other properties.
At press time, there was an unresolved bug in the process for adding the first record to a table. The result of the bug is that if you don't save the first record after adding the first field, and before adding the second field, then Access increments the record ID value for each field you add to the first record. If you add seven fields, Access assigns the value "7" to the ID field of the first record. To avoid this bug, simply click the record selector after adding a value to the first field of the first record in the table. This will save the record and Access will assign a value of "1" to the ID field. Then continue adding the rest of your fields.
Click in the empty cell below Add New Field
, type Big Things Freight
, and then press
to move to the next cell.
Access automatically assigns the value "1" to the ID field, assigns the name "Field1" to the first column, and moves the Add New Field heading to the third column. The Unsaved Record icon (two dots followed by a pencil) in the Record Selector box at the left of the record indicates that this record has not yet been saved.
Type the following information into the next six cells, pressing
after each entry:
345 S. 34th St.
As the insertion point moves out of each cell, its name changes to Field
followed by a number.
Double-click the ID column name, and then type ShipperID to rename it.
Repeat Step 6 for the other columns, changing the column names to the following:
On the View
toolbar in the lower-right corner of the program window, click the Design View
button to switch to Design view.
The buttons displayed on the View toolbar change depending on the type of object that is active. They are a handy way to switch views if the pointer is near the bottom of the window. Most instructions to switch views in this book refer to the View button in the Views group on the Home tab.
Access prompts you to provide a name, because you need to save the table before changing to Design view.
In the Save As
dialog box, type Shippers
, and then click OK
In Design view, the top portion of the window contains a list of the table's fields. The Field Name column contains the names you specified when you created the table. The Data Type column specifies the type of data that the field can contain. The Description column can contain a description of the field.
You can use field names that include spaces, but this can affect how queries and modules have to be written, so it is best not to do so.
Notice the Primary Key icon (a key and right-pointing arrow) to the left of the ShipperID field. The value in the primary key field is used to uniquely identify each record; that is, no two records can have the same value in this field. You can enter this value yourself, or you can let Access help you with this chore. When the data type of a field is set to AutoNumber, as it is here, Access fills this field in every new record with the next available number.
If you no longer want the table to have a primary key, select the field designated as the primary key in the top portion of the window, and on the Design tab, click Primary Key. If you want to assign a different field as the primary key, select that field, and click Primary Key on the Design tab to make it the primary key and move the icon to that field.
Click the Data Type
for the ZIP
field, click the arrow that appears, and then in the list, click Text
If you use only five-digit ZIP codes, the numeric data type would be fine. But setting it to Text is a good idea so users can enter ZIP codes in the ZIP + 4 format.
Click each field name in turn, and then in the Field Properties
area, change the Field Size
to the following:
If you change any field properties that might cause data to be lost (for example, making the size of the field smaller), Access will warn you of this when you attempt to save the table.
CLOSE the table, saving your changes, and then close the database.