Create Databases and Simple Tables
- 7/15/2010
- Creating Databases from Templates
- Creating Databases and Tables Manually
- Manipulating Table Columns and Rows
- Refining Table Structure
- Creating Relationships Between Tables
- Key Points
Creating Databases and Tables Manually
Suppose you need to store different types of information for different types of people. For example, you might want to maintain information about employees, customers, and suppliers. In addition to the standard information—such as names, addresses, and phone numbers—you might want to track these other kinds of information:
Employee identification numbers, hire dates, marital status, deductions, and pay rates
Customer orders and account status
Supplier contacts, current order status, and discounts
You could start with a template, add fields for all the different items of information to a single Contacts table, and then fill in only the relevant fields for each type of contact. However, cramming all this information into one table would soon get pretty messy. It’s better to create a new database based on the Blank Database template and then manually create separate tables for each type of contact: employee, customer, and supplier.
When you create a new blank database or insert a new table into an existing database, the table is displayed on a tabbed page in Datasheet view with one empty row that is ready to receive data. Because the active object is a table, Access adds the Table Tools contextual tabs to the ribbon so that you can work with the table.
A new table in a new database.
If you close the table at this point, it will disappear, because it contains no data and it has no structure. The simplest way to make the table part of the database is to create at least one record by entering data, which simultaneously defines the table’s structure.
Obviously, to create a record, you need to know how to enter information in Datasheet view.
The first record in a new table, before data is entered.
Every table has an empty row that is ready to receive a new record, as indicated by the New icon (the asterisk) in the record selector at the left end of the row. By default, the first field in each new table is an ID field designed to contain an entry that will uniquely identify the record. Also by default, this field is designated as the table’s primary key. No two records in this table can have the same value in this primary key field. Behind the scenes, the data type of this field is set to AutoNumber, so Access will enter a sequential number in this field for you.
The first field you need to be concerned about is the active field labeled Click To Add. You enter the first item of information for the new record in the first cell in this field, and then press the Tab or Enter key to move to the first cell in the field to the right. Access then assigns the value 1 to the ID field, assigns the name Field1 to the second field, and moves the Click To Add label to the third field. The icon in the record selector at the left end of the record changes to two dots and a pencil to indicate that this record has not yet been saved, and the New icon moves to the record selector of the next row.
The first record in a new table, after data has been entered in the first field.
When creating a new table in Datasheet view, you need to save the first record after entering the first item of data. If you don’t, Access increments the ID value for each field you add to that record. For example, if you add seven fields, Access assigns the value 7 to the ID field of the first record. To avoid this problem, you simply click the icon in the record selector after you enter your first value in the first record. This saves the record with the value 1 assigned to the ID field, and subsequent records will be numbered sequentially.
Having entered the first item of data and saved the record, you continue entering items of information in consecutive cells and pressing Tab or Enter. When you finish entering the last item for the first record, you click anywhere in the row below to tell Access that the record is complete.
After you complete the first record of a new table, you will probably want to change the default field names to something more meaningful. To rename a field, you simply double-click its field name and then type the name you want.
At any time while you are entering data in a new table, you can save the table by clicking the Save button on the Quick Access Toolbar and naming the table. If you try to close the table without explicitly saving it, Access prompts you to save the table. If you click No, Access discards the table and any data you have entered.
After you have saved the table for the first time, Access automatically saves each record when you move away from it. You don’t have to worry about losing your changes, but you do have to remember that most data entries can be undone only by editing the record.
Databases almost always contain more than one table. You can create additional empty tables by clicking the Table button in the Tables group on the Create tab of the ribbon. If you need to create a table that is similar in structure to an existing one, you can copy and paste the existing table to create a new one. When you paste the table, Access gives you the option of naming the table and of specifying whether you want the new table to have the existing table’s structure or both its structure and its data.
For some kinds of tables, Access provides Quick Start fields that you can use to add common sets of fields or kinds of fields to a table. The Quick Start options take the work out of defining these fields and can be very useful when you know exactly what type of field you need.
In this exercise, you’ll create a blank database, enter information into the first record of its default table, assign field names, add another record, and save and close the table. Then you’ll copy that table to create a second one. Finally, you’ll create a new table and experiment with Quick Start fields.
In the center pane of the New page, in the Available Templates area, click Blank Database.
In the right pane, click the File Name box, and type MyTables. Then click the Browse button, navigate to your Chapter02 practice file folder, and click OK.
In the right pane, click the Create button.
Access creates the blank database in the specified location, opens the database, and displays a new blank table named Table1.
With the empty field below Click to Add selected, type Scott, and then press Tab to move to the next field.
The icon in the record selector changes to indicate that this record has not yet been saved. The value 1 appears in the ID field, the name of the second column changes to Field1, and the Click To Add label moves to the third column.
Click the icon in the record selector to save the record before you move on.
Click the cell under Click to Add, and type the following information into the next seven cells, pressing Tab after each entry:
Gode
612 E. 2nd
Pocatello
ID
73204
USA
208 555-0161
As the cursor moves to the next cell, the name of the field in which you just entered data changes to Field followed by a sequential number.
The first complete record.
Double-click the ID field name (not the ID value in Field5), and then type CustomerID to rename it.
Repeat step 7 for the other fields, changing the field names to the following:
Field1
FirstName
Field2
LastName
Field3
Street
Field4
City
Field5
State
Field6
ZIP
Field7
Country
Field8
Phone
The table now has intuitive field names.
The renamed fields.
Add another record containing the following field values to the table, pressing Tab to move from field to field:
FirstName
John
LastName
Frederickson
Street
43 rue St. Laurent
City
Montreal
State
Quebec
ZIP
(press Tab to skip this field)
Country
Canada
Phone
514 555-0167
At the right end of the tab bar, click the Close button.
When Access asks whether you want to save the design of the table, click Yes.
Access displays the Save As dialog box.
You must save the table before closing it.
In the Table Name box, type Customers, and then click OK.
Access closes the table, which is now listed in the Tables group on the Navigation bar.
The database now contains one table.
In the Navigation pane, click the Customers table to select it.
On the Home tab, in the Clipboard group, click the Copy button. Then click the Paste button.
The Paste Table As dialog box opens.
If you need to create a table that is similar to an existing table, it is sometimes easier to customize a copy than to create it from scratch.
In the Table Name box, type Employees. In the Paste Options area, click Structure Only to capture the fields from the Customers table but none of the customer information. Then click OK.
The new Employees table appears in the Navigation pane.
Double-click Employees to open it in Datasheet view so that you can view its fields. Then close the table again.
On the Create tab, in the Tables group, click the Table button.
Access creates a new table containing an ID field and a Click To Add field placeholder.
With the Click to Add field active, on the Fields contextual tab, in the Add & Delete group, click the More Fields button.
The More Fields gallery appears.
The Quick Start fields are at the bottom of the More Fields gallery.
If necessary scroll to the bottom of the gallery, and then under Quick Start, click Name.
Access inserts ready-made LastName and FirstName fields.
Repeat steps 18 and 19 to add the Address fields from the Quick Start list.
Access inserts ready-made Address, City, State Province, ZIP Postal, and Country Region fields.
Close the table, saving it with the name Shippers when prompted.