Tables

[ LiB ]

Access stores data in tables. You need to know how to create and modify them. Tables are also known as datasheets : both terms are used interchangeably. In this section, you'll learn the following:

• How to create and modify a table

• How to define a table's primary key

• How to exercise control by setting field properties

• How to define and set relationships between related tables

Main Operations

A table has one purposeto store data. To open a table and view the data, simply double-click it in the Database window. You need to know how to create and modify a table by adding fields and specifying data types. In addition, you should know how to add, modify, and delete data via the table and how to navigate from field to field and from record to record.

Creating Tables

Create and save a table and specify fields with their data types.

There are several ways to create a table. You can open a blank table in view form and start entering data immediately. Or you can open a blank table in design view and set properties before you enter data. To open a table in view mode, double-click the Create Table by Entering Data shortcut in the Database window's tables list. At this point, you can start entering data and Access tries to guess the field's most appropriate data type. To enter data, simply select a cell and enter a value. Click Tab to move to the next field and enter another value, and continue in this fashion until you've entered all your data.

The table in Figure 6.6 shows two fields; one contains an alphanumeric value, and the second contains only numeric digits. Access automatically defines the first field as a text field and the second as a number field. You can change the settings later if necessary.

Figure 6.6. Enter data directly into the table.

At any time, you can switch the table to design view so you can set specific field and table properties. To do so, click the View button on the Table Datasheet toolbar. To save the table and the data, choose Save from the File menu or press Ctrl+S. In the resulting dialog box, enter a name for the table, and click OK. Or, close the table; if there are unsaved changes or additions, Access prompts you to save the table. After saving the table, close it and reopen it to remove the extraneous field columns .

There are other ways to create a new table:

• Choose Table from the Insert menu to display the New Table dialog box.

• Click the Tables shortcut on the Objects bar, and then click New or Design to display the New Table dialog box.

• Double-click the Create Table in Design View shortcut in the Database window.

• Double-click the Create Table by Using Wizard shortcut in the Database window. This action helps you build the new table.

You'll be expected to know how to create, view, modify, and delete the different types of database objects on the exam.

Adding and Deleting Data

Add, delete records in a table.

To add a new record, select a field and type the entry. Press Tab to move to the next field. Continue in this manner until you've entered a value for each field. (You might want to leave a field blank occasionally.) Press Shift+Tab to move to the previous field.

To move to the next record, you can use the mouse to click the first field in the next record. You can use the Tab key to cycle through all the fields. From the last field in the table, pressing Tab selects the first field in the next record. When you save a record, Access automatically displays the new record row so you can enter the next record.

To delete a record from a table, click in the gray area just to the left of the record (this is called the record selector ). Then press the Delete key or select Delete Record from the Edit menu.

Adding a New Field

Add a field to an existing table.

If you want to add a field to an existing table, the table must be in design view. If the table is open and in view mode, click the View button on the Table Datasheet toolbar. If the table is closed, click the Tables shortcut on the Objects bar, select the table and click Design on the Database Window toolbar.

Figure 6.7 shows the previous table in design view with a newly added field. To add a field to the table, select the first empty row in the Field Name column, type the field's name, and click Tab. At this point, you must specify the field's data type: remember, you choose it by selecting a data type from the Data Type field's drop-down list. (Access did it for you earlier when you entered a value directly into the table in view mode. As you can see, Access chose appropriately. Field1 is a text field, Field2 is a number field.) Once you choose a data type, Access displays the appropriate properties in the Field Properties pane. You can adjust these settings as necessary. When you're done, close the table and save it.

Figure 6.7. Use design view to alter a table's design.

Adding and Modifying Records

Add, modify data in a record.

The sample table has a new field, and right now that field contains no data. If the table is still in design view, click View. If it's closed, double-click the table in the Database window. Once the table is in view mode, you can enter new data in the new field or a new record. You can also modify existing data.

To enter new data into the new field, select the first blank field in the column and enter an appropriate value. Press the Down arrow to select the next field in the column and enter a value. When you're ready to enter the next full record, press Tab to select the first field in the new record row. Enter appropriate data and press Tab to move to the next field. Continue in this manner as long as necessary to enter any new data. (Notice that all the extraneous fields are now gone.)

To modify a value, select the appropriate field and replace the value. Simply select the character or characters and enter the new ones. Access overwrites the existing data, character for character. To save the new value, simply move to another record. Before saving the value, you can reject the change by pressing Ctrl+Z.

Deleting Data in a Record

Delete data in a record.

To delete a value, select it and press Delete. At this point, you can enter new data if you like or leave the field blank, as long as the field's Required property is set to No (which means the field can be blank). To delete an entire record, click the record selector (the little gray square to the left of the record) and press Delete. Confirm the action by clicking Yes or click No to cancel the request.

Using Undo

Use the Undo command.

If you make a mistake during data entry or simply change your mind, don't worry. It's easy to make changes. Simply click the Undo toolbar button, but be careful. You must do so before saving the record. Once the new value is saved, you can't use Undo to revert to the previous value or cancel the action. After saving a record, choose Undo Saved Record from the Edit menu.

Navigating the Records

Navigate within a table to next record, previous record, first record, last record.

Use the Tab key to move from field to field. Use the Navigation buttons to move from one record to another. You'll find these buttons at the bottom of the table, on the Navigation toolbar shown in Figure 6.8. All these buttons have small arrows in them.

Figure 6.8. Use navigating buttons to move though the records.

If you want to make a quick jump to a record, enter the record number in the Current record control in the Navigation toolbar and press Enter.

Deleting a Table

Delete a table.

Use caution when deleting tables: there's no way to undo the action and you might lose data you need. If you decide you really can safely delete the table, simply select the table in the Database window and press Delete. When you do, Access asks you to confirm the action. Click Yes to continue or press No to cancel the request.

Saving and Closing a Table

Save and close a table.

You must open a table to save it, but once it is open, you can save the table at any time by clicking Save on the File menu or pressing Ctrl+S. If the table is new and has never been saved, Access asks you to supply a name for the table. To close a table, click the Windows Close button in the table title bar or choose Close from the File menu. If the table has unsaved changes, Access prompts you to save the table.

Define Keys

So far, our discussion of keys has been brief: you know what they are and how Access uses them. You also need to know how to set a table's primary key.

Defining the Primary Key

Define a primary key.

A table must be in design view for you to set its primary key. In design view, select anywhere in the field row that you want to make the primary key and press the Primary Key button on the Table Design toolbar. If the primary key comprises more than one field, select the rows (using Ctrl+click on each row that you want to select) and click the Primary Key tool. Alternately, you can also right-click the row and select Primary Key from the resulting menu. Doing so displays a small key icon to the left of the field rows, as shown in Figure 6.9.

Figure 6.9. Set a primary key field for the table.

Applying an Index

Index a field with, without duplicates allowed.

You also set indexes in design view. Click the Indexes tool on the Table Design toolbar to display the Indexes dialog box, shown in Figure 6.10. If the table contains a primary key, the appropriate index is viewable in this dialog box. To set another index, enter a name for the index, choose the field to which you're adding the index, and then specify a sort order. If you want the index to also be a primary key, choose Yes from the Primary Key property's drop-down list. To create a unique index that rejects duplicate entries in the field, select Yes from the Unique property's drop-down list. A Yes setting for the Ignore Nulls property allows Access to save a record with null values in that field. (You can't apply a Yes setting if the index is on a primary key.)

Figure 6.10. Adding an index to a table.

Table Design/Layout

Controlling data at the table level is fundamental to the database's function. Whenever possible, you want to use the table properties to discern the type of data required and accepted.

Applying Field Attributes

Change field format attributes such as: field size, number format, date format.

Field properties can determine how many characters an entry will accept and how that entry is displayed. These properties are applied at the table level, and the table must be in design view for you to set these properties.

When the field is a text field, simply enter the maximum number of characters any entry can contain as the field's Field Size. The default field size is 255. Why would you bother? Doing so can help you control the data. For instance, if you want to force users to enter the postal abbreviations for states, you could limit a State field's size to 2. That way, if the user enters Kentucky instead of KY, Access rejects the entry.

Number fields also have a Field Size property, but you can't just enter a number. Instead, Access offers a variety of numeric types that limit the size of the acceptable numeric value; Table 6.1 lists those options.

Table 6.1. Number Data Type Field Size Settings

Setting	Smallest Value	Largest Value	Decimal Places
Byte		255
Integer	-32,768	32,767
Long Integer	-2,147,483,648	2,147,483,647
Single	-3.4X10 38	3.4X10 38	7
Double	-1.8X10 308	1.8X10 308	15
Replication ID	N/A	N/A	N/A
Decimal	-1.10 38	1.10 38	28

The Field Size property controls the size of a value. The Format setting determines how that value is displayed. For the most part, you use this property to display numbers and dates. Table 6.2 lists the available formats and gives an example of each.

Table 6.2. Number and Date Formats

Data Type	Format	Explanation	Enter	Displays
Number	General Number	Displays the number as entered	123.456	123.456
	Currency	Displays the regional currency symbol and thousands separator	1234.56	$1,234.56
	Euro	Displays the Euro symbol regardless of regional settings	1234.56	1,234.56
	Fixed	Displays at least one digit; following the regional settings for negative amounts, decimal and currency symbols, and decimal places	1234.56	1234.00 if Field Size set to Integer or Long Integer 1234.56 if Field Size set to Single, Double, or Decimal
	Standard	Same as Fixed but includes thousands separator	1234.56	1,234.00 or 1,234.56 (see Fixed)
	Percent	Multiplies the value by 100 and displays results with the percent sign	1 1.2 .2	100% 120% 20%
	Scientific	Displays scientific notation	1234.56	1.24E+03
Date/Time	General Date	Shows date or time or both using Short Date and Long Time	1/1/04 1/1/04, 01:34:00 PM	1/1/04 1/1/04 1:34:00 PM
	Long Date	Day of week and date	1/1/04	Thursday, January 1, 2004
	Medium Date	Military, abbreviated style	1/1/04	1-Jan-04
	Short Date		1/1/04	1/1/04
	Long Time	All three time components	01:34:00 PM	1:34:00 PM
	Medium Date	Hours and minutes and a.m./p.m.	01:34:00 PM	1:34 PM
	Short Time	Hours and minutes using military clock	01:34:00 PM	13:34

Consequences of Changing Field Size

Understand consequences of changing field size attributes in a table.

Choose the data type that most adequately accommodates the data. Make sure the data type will accept the largest possible value, but don't allocate more space than is really needed. Once you've entered data, you shouldn't change the data type unless it's absolutely necessary. Doing so can have unintended consequences, such as lost data. For instance, if you change a text data to a number field, Access deletes any values that it can't successfully convert to a valid numeric data type.

Changing the Field Size setting can have the same result. Usually, you can increase the size without any problems. On the other hand, if you reduce the size and the field contains values that are larger or greater than the new size, Access deletes those values. If you do decide to make changes, create a backup of the table before you do so, just in case you lose data unexpectedly.

Creating a Validation Rule

Create a simple validation rule for number, text, date/time, currency.

Data types and Field Size settings aren't the only way to control the data a field will store. For instance, you might use a number data type and set the field size property to integer, but that setting has a huge range from -32,678 to 32,767. What if you want to store only values that range from 500 to 700? The data type will weed out non-numeric entries, but integer is the most adequate field size setting you've got and it can't limit the entries to the degree you need.

When you need more control than the data type and field size setting provide, try setting a validation rule. This property requires an expression that expresses the conditions an entry must meet to be accepted and saved. In this case, the expression

 >=500 And <=700 

limits entries to only those values that are equal to or greater than 500 and less than or equal to 700.

You can also create validation rules for other data types. For a text field, you might validate that the data is exactly six characters long with this rule, which uses the ? wildcard to indicate a single character:

 ?????? 

For a date/time field, you can validate that the entry is more recent than 1996:

 >#12/31/1996# 

For a currency field, you could validate that the amount is less than $100:

 <100.0 

Changing Column Widths

Change width of columns in a table.

By default, each field is set to a column width of 15.6667. This might be more space than you need; it might not be enough. Fortunately, it's easy to adjust the column width. Click anywhere inside the column and choose Column Width from the Format menu. Then, in the resulting dialog box, enter the appropriate width and click OK. Or click Best Fit. This option adjusts the column width to display the longest entry in the column.

You can accomplish the same thing with just a few clicks: select the entire column by clicking the column's header (the grey rectangle at the top of the column). Then, grab the header's right border and drag it to the left or right. A double-click assigns the Best Fit setting to the selected column. You can also work with multiple columns.

Changing a column's width doesn't affect the contents or limit the type of data the field will accept in any way. It only changes your view of the data.

Moving a Column

Moving a column within a table.

There are two ways to move a table column. To permanently move a column, open the table in design view, select the row that represents the column you want to move, and then drag that field row up or down, accordingly . Remember, the order of the rows in the design view determines the order the fields occur in the table.

You can make a temporary move to the table itselfwhich adjusts the order you see the fields in the table but doesn't change the permanent order in design view. To temporarily move a column, select the column. Then, click the column's header cell and hold down the mouse button until Access displays the cursor shown in Figure 6.11. Once this cursor appears, drag the column to the new position and release the mouse button. Performing this action can take a bit of practice.

Figure 6.11. Move a column.

Table Relationships

The relationships between tables storing related data is the magic that makes relational databases work. You must know the different types of relationships and how to create them.

The Relationship Types

Create a one-to-one, one-to-many relationship between tables.

The most common relationship that related tables share is the one-to-many relationship. In a one-to-many relationship, the unique primary key value may match one, many, or even no records in a related table. The tables in Figure 6.12 illustrate a one-to-many relationship where each customer can have many records, but each order record tracks to only one customer.

Figure 6.12. Each customer can have "many" records in the Orders table.

A one-to-one relationship isn't as common and, in fact, seldom flows from the actual data. Most often you'll find that one-to-one relationships are forced by business rules. In a one-to-one relationship, there's only one record for each primary/foreign key value in both tables. To force a one-to-one relationship, you must set a unique index on the foreign key field. Remember, a foreign key can be duplicated by theory. However, in this case, the foreign key values must be unique.

To create a permanent relationship, click the Relationships button on the Database toolbar to open the Relationships window. Click the Show Table button on the Relationships toolbar (if necessary) and then add the tables you want to relate. Close the dialog box when you're done.

To create a relationship between two tables, drag a field from one table list to another. Most of the time, you drag the primary key field to the related table's foreign key value. The primary key field is easy to find because Access displays it in bold text. After dragging the primary key field to the related field, Access displays the Edit Relationships dialog box. Notice that the Relationship Type section displays the type of relationship; most RDBMSs do a good job of determining the relationship between two tables. Click the Enforce Referential Integrity option, and then click Create. (A discussion of referential integrity appears in the next section.)

Once you create the relationship and close the dialog box, Access displays what's known as the join line between the two related fields, as shown in Figure 6.13. The infinity sign represents the many side of the relationship; the 1 represents the one side. These symbols are visible because referential integrity is enabled.

Figure 6.13. Use the Relationships window to create permanent relationships between tables.

Deleting Relationships

Delete relationships between tables.

To delete a relationship, select the join line in the Relationships window and press Delete. When Access prompts you to confirm the action, click Yes to continue or No to cancel the request.

Deleting a relationship can have far-reaching and unintended consequences, so we recommend you delete them with great care and only when you have a specific reason to do so. Furthermore, Access won't always let you delete the relationship.

Rules That Determine When You Can Delete Data

Apply rule(s) to relationships such that fields that join tables are not deleted as long as links to another table exist.

In the last section, we told you to check the Enforce Referential Integrity option in the Edit Relationships dialog box. Enabling this option helps protect the validity of your data by limiting when you can enter, modify, and delete records. With this option enabled

• You can't change a primary key value if a related record exists in another table.

• You can't delete a record from the primary key table if a related record exists in another table.

• You can't enter a foreign key value before entering the same value as a primary key value in the related table.

Following our customer/order example, you couldn't change the Alfreds Futterkiste (or any customer) primary key value from ALFKI to any other value if there were records in the Orders table already using a foreign key value of ALFKI. Furthermore, if the value ALFKI didn't exist as a primary key value in the Customers table, you couldn't enter records for that customer in the Orders table. Both actions would produce orphans related records that don't relate to a primary key value. Most systems allow you to create orphans; it's your job to enable referential integrity to prevent them.

[ LiB ]