In Access tables are arguably the most important object. Use tables in Access to store all of your data to run queries and reports.
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 because 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 tutorial, we will be using a sample database. If you would like to follow along, you'll need to download our Access 2016 sample database. You will need to have Access 2016 installed on your computer in order to open the example.
Watch the video below to learn more about working with tables in Access.
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.
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. In our example, the Last Name field is selected, which contains all the last names in the table.
A record is one unit of information. Every cell on a given row is part of that row's record. In our example, Quinton Boyd's record is selected, which contains all of the information related to him in the table.
Each record has its own ID number. Within a table, each ID number is unique to its record and refers to all of the information within that record. The ID number for a record cannot be changed.
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.
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.
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.
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.
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.
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.
The Record Navigation bar allows you to navigate through records one at a time. Click the arrows to move from record to record, and 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 bar at the bottom of the table contains many commands to help you search or scroll through records.
To navigate between fields, you can use the left and right arrow keys or scroll left and right.