5 Creating and Viewing Tables

Tables are the main units of data storage in Access. Recall that a table is made up of one or more columns (or fields) and that a given column may appear in more than one table in order to indicate a relationship between the tables.

From the business example discussed earlier, we concluded that two tables would be sufficient to store the data about Customers and their bank Accounts. We now give the step-by-step instructions for creating these two tables in Access.

There are a number of ways to create a table in Access. Access provides wizards that guide the user through creating a table by suggesting names for tables and columns. The other main way to create a table is by using the Design View to manually define the columns (fields) and their data types.

While using the wizards is a fast way to create tables, the user has less control over the column names (fields) and data types. In this tutorial, we will describe the steps to create a table using the Design View. Students are encouraged to experiment on their own with using the Create Table wizard.

5.1 Creating a Table Using the Design View

1. Click on the New button and highlight Design View in the dialog box that appears:

   
   Then click on the OK button.

2. The Table Design View will appear. Fill in the Field Name, Data Type and Description for each column/field in the table. The CustomerID field is filled in below:

   
   

   Note that the default name given for the table is Table1. In a later step, we will assign an appropriate name for this table.

   Fill in the information for the fields as follows:
   

   Field Name	Data Type	Description
   CustomerID	Number	The Unique Identifier for a Customer
   Name	Text	The Name of the Customer
   Address	Text	The Address of the Customer
   City	Text	The City of the Customer
   State	Text	The home State of the Customer
   Zip	Text	The Zip Code of the Customer

   A figure showing the design view with the new table definition filled in is given below:

   

3. Now that all of the fields have been defined for the table, a Primary Key should be defined. Click on the CustomerID field with the Right mouse button and choose Primary Key from the pop-up menu.

   
   Notice that a small key appears next to the field name on the left side.
   Note: To remove a primary key, simply repeat this procedure to toggle the primary key off.

4. As a final step, the table must be saved. Pull down the File menu and choose the Save menu item. A dialog box will appear where the name of the new table should be specified. Note that Access gives a default name such as Table1 or Table2. Simply type over this default name with the name of the table.
   For this example, name the table: Customer Then click on the OK button.
   

When defining the fields (columns) for a table, it is important to use field names that give a clear understanding of the data contents of the column. For example, does the field CNO indicate a Customer Number or a Container Number ?

Field names in Access can be up to 64 characters long and may contain spaces. However, the use of spaces in field names and table names is strongly discouraged. If you wish to make field names easier to read, consider using an underscore character to separate words. However be certain no spaces appear before or after the underscore.

The following table summarizes some different ways to give field names:

Description	Bad	Good
Unique identifier for a customer	CID	CustomerID or Customer_ID
Description for a product	PDESC	ProductDescription
Employee's home telephone number	Employee_home_telephone_number	HomePhone
Bank account number	BA#	AccountNumber

5.2 Exercise: Creating a Table

1. Click on the New button and highlight Design View in the dialog box that appears. Then click on the OK button.

2. The Table Design View will appear. Fill in the Field Name, Data Type and Description for each column/field in the Accounts table.

   Field Name	Data Type	Description
   CustomerID	Number	The Unique Identifier for a Customer
   AccountNumber	Number	The Unique Identifier for a Bank Account
   AccountType	Text	The type of account (Checking, savings, etc.)
   DateOpened	Date	The date the account was opened
   Balance	Number	The current balance (money) in this account (in $US)

   A figure showing the design view with the new table definition filled in is given below:

   

3. Define a Primary Key for the Accounts table. Click on the AccountNumber field with the Right mouse button and choose Primary Key from the pop-up menu.

4. Save the new Accounts table by pulling down the File menu and choosing the Save menu item. Fill in the name of the table: Accounts Then click on the OK button.

5.3 Viewing and Adding Data to a Table

Data can be added, deleted or modified in tables using a simple spreadsheet-like display. To bring up this view of a single table's data, highlight the name of the table and then click on the Open button.

In this view of the table, shown in the figure below, the fields (columns) appear across the top of the window and the rows or records appear below. This view is similar to how a spreadsheet would be designed.

Note at the bottom of the window the number of records is displayed. In this case, since the table was just created, only one blank record appears.

To add data to the table, simply type in values for each of the fields (columns). Press the Tab key to move between fields within a record. Use the up and down arrow keys to move between records. Enter the data as given below:

CustomerID	Name	Address	City	State	Zip
1001	Mr. Smith	123 Lexington	Smithville	KY	91232
1002	Mrs. Jones	12 Davis Ave.	Smithville	KY	91232
1003	Mr. Axe	443 Grinder Ln.	Broadville	GA	81992
1004	Mr. & Mrs. Builder	661 Parker Rd.	Streetville	GA	81990

To save the new data, pull down the File menu and choose Save.

To navigate to other records in the table, use the navigation bar at the bottom of the screen:

To modify existing data, simply navigate to the record of interest and tab to the appropriate field. Use the arrow keys and the delete or backspace keys to change the existing data.

To delete a record, first navigate to the record of interest. Then pull down the Edit menu and choose the Delete menu item.

To close the table and return to the Access main screen, pull down the File menu and choose the Close menu item.

5.4 Exercise: Adding Data to a Table

Note that when entering the dates, type in the full four digits for the year. By default, Access only displays the last two digits of the year; however, all four digits are stored in the table.

Be sure to save the data when you are done. The figure below shows the Accounts table and data as it should appear when you are done with this exercise.

At this point in the tutorial, we have created two tables, Customers and Accounts, and added data to each one. In the subsequent sections, we will cover how to query and report on the data in the tables and how to create a user-friendly data entry form using the Access wizards.

5.5 Review of Creating and Viewing Tables

1. Click on the Tables tab on the Access main screen
2. Click on the New button.
3. Choose the Design View and click the OK button.
4. Fill in the name, data type and description of each of the fields in the table.
5. Designate a primary key by clicking on one of the fields with the right mouse button and then choose Primary Key from the pop-up menu.
6. Save the table by pulling down the File menu and choosing Save.
7. Close the new table by pulling down the File menu and choosing Close.

To change the design of an existing table (e.g., to add, change or delete a field):

1. Click on the Tables tab on the Access main screen
2. Highlight the name of the table to be modified and click on the Design button.
3. Make the necessary changes.
4. Save the table by pulling down the File menu and choosing Save.
5. Close the table by pulling down the File menu and choosing Close.

To add, delete or change data in an existing table:

1. Click on the Tables tab on the Access main screen
2. Highlight the name of the table to be modified and click on the Open button.
3. Make the necessary changes to the data.
4. Save the table data by pulling down the File menu and choosing Save.
5. Close the table by pulling down the File menu and choosing Close.