Building Queries: Single/Two-Table, Criteria & Comparison Operators

Building Queries: Single/Two-Table, Criteria & Comparison Operators

What Is a Query For?

A query is a database object used to extract and analyse data that meets specific conditions, drawn from one table or from several related tables at once. Unlike a filter, a query is a saved, named object that you can run again and again, and it can combine, calculate, and sort data in ways a simple filter cannot. This makes queries the main tool the ICDL Using Databases module tests for retrieving information for reporting and analysis.

Creating a Single-Table Query

A single-table query pulls data from just one table. To build one in Access:

1. Open Query Design and add the one table you need.
2. Drag the fields you want to see into the design grid, in the order you want them to appear.
3. Type any conditions into the Criteria row under the relevant field (for example, typing >50000 under a Salary field returns only rows where Salary exceeds 50000).
4. Save and name the query so it can be reused later — the syllabus specifically expects you to be able to create and name both single- and two-table queries.

Creating a Two-Table Query

A two-table query draws matching data from two related tables in a single result set — for example, combining a Customers table with an Orders table so you can see each customer's orders side by side. This only works because the tables already share a relationship (a primary key in one table linked to a matching foreign key in the other, as covered in the previous chapter). To build one:

1. Add both tables to the query design grid.
2. Access shows the join line connecting the related fields automatically, provided the relationship already exists.
3. Drag fields from either table into the grid and add criteria exactly as you would for a single-table query.
4. Save and name the query.

Because the query only returns rows where the join condition is satisfied, a two-table query naturally excludes records that have no match in the related table — for instance, a customer with no orders would not appear in a Customers-Orders query result.

Choosing Single-Table or Two-Table

Deciding which type of query to build comes down to where the fields you need actually live:

• If every field you want to see and every condition you want to test belongs to one table, a single-table query is enough — for example, "list every Product where Stock Level is below 10" only needs the Products table.
• If the answer requires combining information that is split across two related tables, you need a two-table query — for example, "list each Order along with the Customer's name and phone number" needs both the Orders table and the Customers table, joined on the customer's ID.

Both query types are built the same way once the correct table(s) are on the design grid: drag in fields, set criteria, and save with a descriptive name — a name like qryActiveCustomersDurban is far more useful later than Query1.

Comparison Operators for Query Criteria

Once a table (or tables) is added to a query, you narrow the results by typing criteria into the Criteria row. The syllabus requires you to recognise and apply the following comparison operators:

These operators work with numbers, dates, and text fields alike, and they are typed directly into the Criteria cell beneath the field you want to filter. Text values are usually typed in quotation marks (="Cape Town"), while date values are usually enclosed in hash symbols (>#2026-01-01#) — the application often adds these characters automatically once you finish typing, confirming the criterion was recognised correctly.

These six comparison operators only ever test one condition on one field at a time. To test several conditions together — whether a record must meet all, only one, or specifically must not meet one — you need the logical operators AND, OR, and NOT, covered in the next section along with wildcards for partial text matches.

Sorting Query Results

A query grid also includes a Sort row beneath each field. Setting a field's sort to Ascending arranges the results from smallest to largest (or A to Z, or earliest to latest date), while Descending reverses that order. You can sort on more than one field at once — Access applies the sort order from left to right across the fields marked with a sort direction, so the leftmost sorted field takes priority. Sorting the query itself (rather than the underlying table) means the original table's record order is never disturbed; only the query's result set is arranged differently each time it runs.

Why Queries Matter for Retrieving Information

A well-built query does far more than a single filter ever could: it can pull matching data from related tables, apply precise numeric, text, or date criteria, and present the results in a consistent, sorted order every time it is run. This combination of selection (which fields), restriction (which records, via criteria) and presentation (sort order) is exactly what the syllabus means when it describes a query's purpose as extracting and analysing data. The next section builds on this by showing how to combine multiple criteria with logical operators, match partial values with wildcards, and edit a query once it already exists.