Access Data Manipulation   «Prev  Next»
Lesson 1

Access Data Manipulation

This course builds on the Building Access Database course to provide a hands-on overview analyzing data with Access 2021.
In "Building Access Database", you learned how to create tables, put data in them, and define the relationships between tables. This course discusses how to work with the data, finding the data you want by filtering and querying, and creating forms and reports to display and analyze data.

Course objectives

After completing the course, you will be able to use Access to:
  1. Find, filter, and sort data
  2. Define queries
  3. Define criteria to limit data
  4. Create summary calculations
  5. Create calculated fields
  6. Create and edit forms
  7. Enter and edit data in a form
  8. Create and edit reports
  9. Create mailing labels
  10. Create HTML files from Access objects
  11. Create a Word mail merge data file from Access data
  12. Back up and restore a database
  13. Compact and repair a database
  14. Print database objects

Filter and query an Access Database Table

Microsoft Access provides several ways to filter and query tables. Here are some options:
  1. Filter By Form: A user-friendly way to filter data. You can select criteria and apply filters using a form.
  2. Filter By Selection: Select a value in the table, then use the "Filter By Selection" option to show only records with that value.
  3. Query By Example (QBE): A graphical query tool that allows you to design queries using a grid.
  4. SQL Queries: Write SQL statements to query the data. This method offers the most flexibility and power.
  5. Query Wizard: A guided tool that helps you create queries.
To get started, open your Access database, select the table you want to query, and use the "Home" tab in the ribbon to access these features.

Filtering MS Access Table Data

If you have ever been to a large, noisy gathering, you might have been struck by how easily humans can ignore the discord of music and voices around them and concentrate on whatever conversation they are having at the time. Our brains somehow filter out the unimportant noise and let in only what we need to hear. This idea of screening out the unnecessary is exactly what Access filters do. We often want to work with only some of the records in a large table. The other records are just noise that we want to somehow tune out. For example, if you have a table of customer invoices, you might want to work with any of the following subsets of the data:
  1. Only those invoices from a particular customer
  2. All the overdue invoices
  3. Every invoice with an amount greater than $1,000

A filter can do all this and more. The idea is that you define the criteria you want to use (such as having the Amount field greater than or equal to 1000), and then, when you filter the table, Access displays only those records that meet the criteria. When you filter a table, the resulting subset of records is called a dynaset[1].
In the next lesson, we will examine the course prerequisites.
[1]dynaset: A dynaset is a temporary set of data taken from one or more tables in the underlying file.

SEMrush Software