Working with Existing Database Table

Tables Introduced

Viewing and Navigating Table Data

Tables are the basis of everything that you do in Access. Most of the data for a database resides in tables, so if you're creating an employee payroll database, the employee data will be stored in a table, your payroll codes might be stored in a table, and your past payroll records could be stored in a table. A table contains data about a specific topic or subject (for example, customers, orders, or employees). Tables are arranged in rows and columns, similarly to a spreadsheet. The columns represent the fields, and the rows represent the records

Editing Table Data

You can change the data in a table any time that you are in Datasheet view of a table, the result of a query, or Form view of a form. Access saves changes you make to a record as soon as you move off the record.

Modifying Table Data

One task you might want to perform is to simply modify table data. Here's the process:

1. Select the record you want to change by using any of the techniques listed in

2. Select the field you want to change by clicking the field or using the arrow keys.

3. Type to make the necessary changes to the data. When you move off the record, Access saves your changes.

Deleting Field Contents

Now that you know how to modify the contents of a field, let's talk about how to delete the contents of a field. The process is simple:

1. Select the field contents you want to delete.

2. Press the Delete key. Access deletes the contents of the field.

It's important to note that Access saves changes to the current record as soon as you move off the current record. If you want to cancel all the changes you made to a record, you simply press the Esc key twice. Access cancels all changes you made to that record.

Undoing Changes

There are different options available when you're undoing changes to a field or to a record. The options differ, depending on whether you are still within a field, have left the field, or have left the record. The sections that follow explore the various options that are available.

Undoing Changes Made to the Current Field

When you are in the process of making changes to a field, you might realize that you really didn't want to make changes to that field or to that record. To undo changes to the current field, you can either click the Undo tool on the toolbar, select Edit | Undo Typing, or press the Esc key once. For example, if you meant to change the contact first name from Alison to Sue but realized that you were accidentally typing Sue in the Customer field, you could press the Esc key once, click the Undo tool on the toolbar, or select Edit | Undo Typing to undo your change.

Undoing Changes After You Move to Another Field

The process of undoing changes after you move to another field is slightly different from the process of undoing changes made to the current field. You can either click the Undo tool on the toolbar, select Edit | Undo Current Field/Record, or press the Esc key once. For example, if you meant to change the contact first name from Alison to Sue but realized that you accidentally typed Sue in the Customer field, and then you moved to another field, you could click the Undo tool on the toolbar, press the Esc key once, or select Edit | Undo Current Field/Record to undo your change.

Undoing Changes After You Save a Record

When you make changes to a field and then move to another record, Access saves all changes to the modified record. As long as you do not begin making changes to another record, you can still undo the changes you made to the most recently modified record. To do this, you can either click the Undo tool on the toolbar, select Edit | Undo Saved Record, or press the Esc key twice. For example, if you meant to change the contact first name from Alison to Sue but realized that you accidentally typed Sue in the Customer field, and you then moved to another record, you could click the Undo tool on the toolbar, select Edit | Undo Saved Record, or press the Esc key twice to undo your change.

Adding Records to a Table

Access adds records to the end of a table, regardless of how you add them to the table.

To add records, follow these steps:

1. Select the table to which you want to add information.

2. Click the New Record Navigation button at the bottom of the Datasheet window.

3. Add the necessary information to the fields within the record. When you move off the record, Access saves the new record.

Deleting Records Before you can delete records, you must select them. The following sections therefore cover the process of selecting records and then the process of deleting records. Selecting One or More Records To select one record, you simply click the gray record selector button to the left of the record within the datasheet. To select multiple records, you click and drag within the record selector area. Access selects the contiguous range of records in the area over which you click and drag. As an alternative, you can click the gray selector button for the first record you want to select, hold down the Shift key, and then click the gray selector button of the last record that you want to select. When you do this, Access selects the entire range of records between them.

Finding and Replacing Records

When you are working with records in a large data table, you often need a way to locate specific records quickly. By using the Find feature, you can easily move to specific records within a table. After you have found records, you can also replace the text within them.

Finding a Record That Meets Specific Criteria

The Find feature allows you to search in a datasheet for records that meet specific criteria. Here's how it works:

1. Select the field containing the criteria for which you are searching.

2. Click the Find button on the toolbar. The Find and Replace dialog box appears
   

3. Type the criteria in the Find What text box.

4. Use the Look In drop-down list box to designate whether you want to search only the current field or all fields in the table.

5. Use the Match drop-down list box to designate whether you want to match any part of the field you are searching, the whole field you are searching, or the start of the field you are searching. For example, if you type Federal in the Find What text box and you select Whole Field in the Match drop-down list box, you find only entries where Ship Via is set to Federal. If you select Any Part of Field, you find Federal Shipping, Federal Express, United Federal Shipping, and so on. If you select Start of Field, you find Federal Shipping and Federal Express, but you do not find United Federal Shipping.

6. Use the Search drop-down list box to designate whether you want to search only up from the current cursor position, only down, or in all directions.

7. Use the Match Case check box to indicate whether you want the search to be case-sensitive.

8. Use the Search Fields as Formatted check box to indicate whether you want to find data only based on the display format (for example, 17-Jul-96 for a date).

9. Click the Find Next button to find the next record that meets the designated criteria.

10. To continue searching after you close the dialog box, use the Shift+F4 keystroke combination.

Replacing Data in a Table

There may be times when you want to update records that meet specific criteria. You can use the Replace feature to automatically insert new information into the specified fields. Here's the process:

1. Click within the field that contains the criteria you are searching for.

2. Click the Find button on the toolbar. The Find and Replace dialog box appears.

3. Select the Replace tab
   

4. Type the criteria in the Find What text box.

5. Type the new information (the replacement value) in the Replace With text box.

6. Choose values for the Look In drop-down list box, Match drop-down list box, Search drop-down list box, Match Case check box, and Search Fields as Formatted check box, as described in the "Finding a Record That Meets Specific Criteria" section of this hour.

7. Click the Find Next button. Access locates the first record that meets the criteria designated in the Find What text box.

8. Click the Replace button. Access replaces the text for the record and finds the next occurrence of the text in the Find What text box.

9. Repeat step 8 to find all occurrences of the value in the Find What text box and replace them. As an alternative, you can click the Replace All button to replace all occurrences at once.

   You should use Replace All with quite a bit of caution. Remember that the changes you make are permanent. Although Replace All is a viable option, when you use it you need to make sure you have a recent backup and that you are quite certain of what you are doing. In fact, I usually do a few replaces to make sure that I see what Access is doing before I click Replace All.

   
   

10. Click Cancel when you're done.

If you are searching a very large table, Access can find a specific value in a field fastest if the field you are searching on is the primary key or an indexed field.

With either Find or Replace, you can use several wildcard characters. A wildcard character is a character you use in place of an unknown character. Table 2 describes the wildcard characters.

Table 2 Wildcard Characters You Can Use When Searching

Wildcard Character	Description
*	Acts as a placeholder for multiple characters.
?	Acts as a placeholder for a single character.
#	Acts as a placeholder for a single number.

Filtering Table Data

In a table you can apply filters to fields to limit what records you view. This is very helpful if you want to view just the data associated with a subset of the records. For example, you might want to view just the data associated with sales managers.

Filtering by Selection

The Filter by Selection feature allows you to select text and then filter the data in the table to that selected text. To use the Filter by Selection feature, follow these steps:

1. Open a table in Datasheet view.

2. Select the record and field in the table that contain the value on which you want to filter.

3. Click the Filter by Selection button. The data is filtered to only the specified rows.