Access 2013

Introduction

Video: Working with Tables

Watch the video (4:25). Need help?

While there are four types of database objects in Access, tables are arguably the most important. Even when you're using forms, queries, and reports, you're still working with tables since that's where all of your data is stored. Tables are at the heart of any database, so it's important to understand how to use them.

In this lesson, you will learn how to open tables, create and edit records, and modify the appearance of your table to make it easier to view and work with.

Throughout this course, we will be using a sample database. If you would like to follow along, you'll need to download our Access 2013 sample database. You will need to have Access 2013 installed on your computer in order to open the example.

Table basics

To open an existing table:

1. Open your database, and locate the Navigation pane.
2. In the Navigation pane, locate the table you would like to open. Tables are marked with the icon.
3. Double-click the desired table. It will open and appear as a tab in the Document Tabs bar.
   Opening a table

Understanding tables

All tables are composed of horizontal rows and vertical columns, with small rectangles called cells in the places where rows and columns intersect. In Access, rows and columns are referred to as records and fields.

Records, fields, and cells in an Access table

A field is a way of organizing information by type. Think of the field name as a question, and every cell within that field as a response to that question.

Fields and field names

A record is one unit of information. Every cell on a given row is part of that row's record. Each record has its own ID number. Within a table, each ID number is unique to its record and refers to all the information within that record. The ID number for a record cannot be changed.

Records and record ID numbers

Each cell of data in your table is part of both a field and a record. For instance, if you had a table of names and contact information, each person would be represented by a record, and each piece of information about each person—name, phone number, address, and so on—would be contained within a distinct field on that record's row.

Click the buttons in the interactive below to learn how to navigate a table.

Field

In Access, columns are referred to as fields. When you organize your data by entering it into different fields, you are organizing it by type. Each field contains one type of data. The type of data contained within a field is described by the title at the top of each field.

Record

In Access, table rows are referred to as records. A record is a unit of data that includes every piece of information in a given row.

Record Navigation Bar

The Record Navigation bar allows you to navigate through records one at a time. Click the arrows to move from record to record, navigate directly to a record by searching for it with the record search box. You can also use the Record Navigation bar to add a new record to the current table.

The Records Command Group

The Records command group on the Home tab contains a handful of commands for working with records, including New Record, Save Record, and Delete Record.

Find and Replace

The Find command allows you to search for any term in your table.

The Replace command allows you to search for any term in your table and replace it with a new one. For example, you could replace the word Fall with the word Autumn.

The Text Formatting Command Group

These formatting options allow you to make your table easier to read by adding an alternate row color or modifying the gridlines that separate individual rows and columns.

You can access even more formatting options by clicking the arrow in the bottom right corner.

Navigating within tables

To navigate through records in a table, you can use the up and down arrow keys, scroll up and down, or use the arrows in the record navigation bar located at the bottom of your table. You can also find any record in the currently open table by searching for it using the record search box. Simply place your cursor in the search box, type any word that appears in the record you would like to find, and press the Enter key. To view additional records that match your search, press Enter again.

Using the record navigation bar

To navigate between fields, you can use the left and right arrow keys or scroll left and right.

Adding records and entering data

Entering data into tables in Access is similar to entering data in Excel. To work with records, you'll have to enter data into cells. If you need help entering data into records, you might want to review our Excel 2013 Cell Basics lesson.

To add a new record:

There are three ways to add a new record to a table:

• In the Records group on the Home tab, click the New command.
  Adding a new record from the Ribbon
• On the Record Navigation bar at the bottom of the window, click the New record button.
  Adding a new record from the Record Navigation bar
• Begin typing in the row below your last added record.
  Adding a new record by typing in the row under the last record

Occasionally when you enter information into a record, a window will pop up to tell you that the information you've entered is invalid. This means the field you're working with has a validation rule, which is a rule about the type of data that can appear in that field. Click OK, then follow the instructions in the pop-up window to re-enter your data.

An example of a validation warning

To save a record:

Access is designed to save records automatically. After you enter a record, you can either select a different record or close the object, and Access will save the record. However, in certain situations, you many want to save a record manually. For example, if you needed to edit an existing record, you could save the record to ensure your changes are saved.

1. Select the Home tab, and locate the Records group.
2. Click the Save command. The record will be saved.
   
   Saving a record

Editing records

To quickly edit any record within a table, you can click on it and type in 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.
   Clicking the Replace command
3. In the Find What: field, type the word you would like 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.
   
   Entering the replacement text
4. Click the Look In: drop-down arrow to select the area you would like to search.
   • Select Current Field to limit your search to the currently selected field.
   • Select Current Document to search within the entire table.
   Choosing 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.
   Choosing how closely the records should match your search
6. Click Find Next. If the text is found, it will be selected.
   Clicking 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.
   
   Replacing 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.

The 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.
   Selecting a record
2. Select the Home tab, and locate the Records group.
3. Click the Delete command.
   Clicking the Delete Command
4. A dialog box will appear. Click Yes.
   
   Deleting 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...".

A missing ID number after a record has been deleted

Modifying table appearance

Video: Customizing Tables

Watch the video (2:38). Need help?

Access offers various ways to modify the appearance of tables, including resizing fields and rows and temporarily hiding information you don't need to see. These changes aren't just about making your table look "pretty"—they also can make the table easier to read.

Resizing fields and rows

If your fields and rows are too small or large for the data contained with them, you can always resize them so all of the text is displayed.

To resize a field:

1. Place your cursor over the right gridline in the field title. Your mouse will become a double arrow.
   Resizing a field
2. Click and drag the gridline to the right to increase the field width or to the left to decrease the field width, then release the mouse. The field width will be changed.
   The resized field

To resize a row:

1. Place your cursor over the bottom gridline in the gray area to the left of the row. Your mouse will become a double arrow.
   Resizing a row
2. Click and drag the gridline downward to increase the row height or upward to decrease the row height, then release the mouse. The row height will be changed.
   The resized row

Hiding fields

If you have a field you don't plan on editing or don't want other people to edit, you can hide it. A hidden field is invisible but is still part of your database. Data within a hidden field can still be accessed from forms, queries, reports, and any related tables.

To hide a field:

1. Right-click the field title, then select Hide Fields.
   Hiding a field
2. The field will be hidden.
   
   

If you decide you would like the field to be visible again, you can unhide it. Simply right-click any field title, then select Unhide Fields. A dialog box will appear. Click the check boxes of any fields you would like to be visible again, then click Close.

Unhiding a hidden field

Table formatting options

Alternate row color

By default, the background of every other row in an Access table is a few shades darker than the background of the rest of the table. This darker alternate row color makes your table easier to read by offering a visual distinction between each record and the records directly above and below it.

A table with alternate row colors

To change the alternate row color:

1. Select the Home tab, locate the Text Formatting group, and click the Alternate Row Color drop-down arrow.
   Clicking the Alternate Row Color drop-down arrow
2. Select a color from the drop-down menu, or select No Color to remove the alternate row color.
   Choosing a row color
   
3. The alternate row color will be updated.

The updated alternate row color

Modifying gridlines

Another way Access makes your tables easier to read is by adding gridlines that mark the borders of each cell. Gridlines are the thin lines that appear between each cell, row, and column of your table. By default, gridlines are dark gray and appear on every side of a cell, but you can change their color, or hide undesired gridlines.

Horizontal and vertical gridlines

To customize which gridlines appear:

1. Select the Home tab, locate the Text Formatting group, and click the Gridlines drop-down arrow.
   The Gridlines drop-down arrow
2. Select the gridlines you would like to appear. You can choose to have horizontal gridlines between the rows, vertical gridlines between the columns, both types of gridlines, or none at all.
   Selecting horizontal gridlines
3. The gridlines on your table will be updated.
   The updated horizontal-only gridlines

Additional formatting options

To view additional formatting options, click the Datasheet Formatting arrow in the bottom-right corner of the Text Formatting group.

Clicking the Datasheet Formatting arrow

The Datasheet Formatting dialog box offers many advanced formatting options, including the ability to modify background color, gridline color, and border and line style. It even includes the ability to view a sample table with your formatting choices, so play around with the various formatting options until you get your table looking the way you want it.

Changing the table background color, gridline color, and border and line style

Challenge!

1. Open an existing Access database. If you want, you can use our Access 2013 sample database.
2. Open the Customers table.
3. Add a new record to the table. Be sure to enter data for every field.
4. Find the record with the name Tyra Kirby, and replace it with a name of your choice.
5. Hide a field, then unhide it.
6. Change the alternate row color.