Working with Tables

In Access tables are arguably the most important object. Use tables in Access to store all of your data to run queries and reports.

Editing records

To quickly edit any record within a table, you can click it and type your changes. However, Access offers you the ability to find and replace a word within multiple records and delete records entirely.

To replace a word within a record:

You can edit multiple occurrences of the same word by using Find and Replace, which searches for a term and replaces it with another term.

  1. Select the Home tab, and locate the Find group.
  2. Select the Replace command. The Find and Replace dialog box will appear.
    Screenshot of Access 2013Clicking the Replace command
  3. In the Find What: field, type the word you want to find, then in the Replace With: field type the word you would like to replace the original word. In our example, we'll find instances of the word Fall and replace it with Autumn.

    Screenshot of Access 2013Entering the replacement text
  4. Click the Look In: drop-down arrow to select the area you want to search.
    • Select Current Field to limit your search to the currently selected field.
    • Select Current Document to search within the entire table.
    Screenshot of Access 2013Choosing where in the table to look
  5. Click the Match: drop-down arrow to select how closely you'd like results to match your search.
    • Select Any Part of Field to search for your search term in any part of a cell.
    • Select Whole Field to search only for cells that match your search term exactly.
    • Select Beginning of Field to search only for cells that start with your search term.
    Screenshot of Access 2013Choosing how closely the records should match your search
  6. Click Find Next. If the text is found, it will be selected.
    Screenshot of Access 2013Clicking Find Next to view the next match for your search
  7. Review the text to make sure you want to replace it. Click Replace to replace the original word with the new one.

    Screenshot of Access 2013Replacing the original word with a new one
  8. Access will move to the next instance of the text in the object. When you are finished replacing text, click Cancel to close the dialog box.

The Replace All option is powerful, and it may actually change some things you don't want to change. In the example below, the word fall did not refer to the season, so replacing it with Autumn would be incorrect. Using the normal Replace option allows you check each instance before replacing the text. You can click Find Next to skip to the next instance without replacing the text.

Screenshot of Access 2013The unintended consequences of choosing Replace All

To delete a record:

  1. Select the entire record by clicking the gray border on the left side of the record.
    Screenshot of Access 2013Selecting a record
  2. Select the Home tab and locate the Records group.
  3. Click the Delete command.
    Screenshot of Access 2013Clicking the Delete Command
  4. A dialog box will appear. Click Yes.

    Screenshot of Access 2013Deleting a record
  5. The record will be permanently deleted.

The ID numbers assigned to records stay the same even after you delete a record. For example, if you delete the 213th record in a table the sequence of record ID numbers will read ...212, 214, 215... rather than ...212, 213, 214, 215...

Screenshot of Access 2013A missing ID number after a record has been deleted