18.2 Manipulate Data

2026 Syllabus Objectives

  • Perform calculations: Use arithmetic operations or numeric functions to perform calculations including calculated fields, calculated controls. Use formulae and functions to perform calculations at run time including addition, subtraction, multiplication, division, sum, average, maximum, minimum, count.
  • Sort data: Use a single criterion, or multiple criteria to sort data into ascending or descending order.
  • Search and select data: Use a single criterion, or multiple criteria to select subsets of data using a query. Perform searches using a variety of operators including AND, OR, NOT, LIKE, >, <, =, >=, <=, <>. Perform searches using wildcards.

Performing Searches with Queries 🔍

https://cambridgeassistant.com

What is a Query?

A query is a powerful tool in database management systems like Microsoft Access that allows you to search for and select specific subsets of data from your tables. Each query can be saved and reused, automatically updating to include any new data added to the table.

Creating a Query

To perform a search in Access:

  1. Select the Create tab
  2. Navigate to the Other section
  3. Click on the Query Wizard icon
  4. Select Simple Query Wizard from the New Query window
  5. Ensure the correct table is selected in the Tables/Queries box
  6. Move required fields into the query using the arrow keys

Query Views

Design View is used to set up or modify the structure of a query, including adding search criteria. To filter results, move the cursor into the Criteria: row of a specific field and type the search term.

Datasheet View displays the results of the query in a table format, showing all records that match your search criteria.

Single Criterion Search

A single criterion search filters data based on one condition. For example, to find all cars made by Ford, you would:

  • Open the query in Design View
  • Type Ford in the Criteria: row of the Make field

This extracts only the records where the Make field contains "Ford".

Multiple Criteria with AND Logic

An AND query requires multiple criteria to be met simultaneously. When criteria are entered on the same row in Design View, Access uses AND logic.

Example: To find all Fords that need valeting:

  • Enter Ford in the Criteria row for the Make field
  • Enter Yes in the Criteria row for the Valet field (on the same row)

Both conditions must be true for a record to be selected.

Sign in to view full notes