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, simply click it and type your changes. However, Access also offers you the ability to find and replace a word within multiple records, as well as to 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.
    The Find commandThe Replace command
  3. Click the Find What: box, and type the word you want to find.
    Entering the term to findEntering the term to find
  4. Click the Replace With: box, and type the word you want to replace the original word.
    Entering the replacement textEntering the replacement text
  5. 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 the entire table.
    Selecting where in the table to lookChoosing where in the table to look
  6. 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.
    Choosing how closely the records should match your searchChoosing how closely the records should match your search
  7. Click Find Next to find the next occurrence of your search term.
    Clicking Find Next to view the next match for your searchClicking Find Next to view the next match for your search
  8. Click Replace to replace the original word with the new one.
    Replacing the original word with a new oneReplacing the original word with a new one

While you can use Replace All to replace every instance of a term, replacing them one at a time allows you to be absolutely certain you edit only the data you want. Replacing data unintentionally can have a negative impact on your database.

The unintended consequences of choosing Replace AllThe unintended consequences of choosing Replace All

To delete a record:

  1. Select the entire record by clicking the gray border at the left side of the record.
    Selecting a recordSelecting a record
  2. Select the Home tab, and locate the Records group.
  3. Click the Delete command. The record will be permanently deleted.
    The Delete CommandThe Delete Command

The ID numbers assigned to records stay the same even after you delete a record. For example, if you delete the 34th record in a table, the sequence of record ID numbers will read ...32, 33, 35, 36... rather than ...32, 33, 34, 35, 36...

A missing ID number after a record has been deletedA missing ID number after a record has been deleted