Designing Queries Header

Introduction

While tables store your data, the true analytical power of a relational database is revealed through the query. Have you ever wondered how a business can instantly isolate a list of high-value customers from a database containing thousands of records? Queries allow you to retrieve and compile information from one or more tables based on specific, logical conditions you define. In this lesson, you will learn the fundamental steps to construct a one-table query, turning raw data into actionable insights.

Workflow Resource: To follow this lesson precisely, we recommend using our Access sample database. Ensure you have Microsoft Access installed to interact with the file.

Watch the video below to master the architectural logic of simple queries.

What are queries?

At its core, a query is a sophisticated search tool. Running a query is akin to asking your database a detailed question. By defining specific search conditions, you instruct Access to sift through your data and present only the information that meets your requirements.

How are queries used?

Queries are significantly more robust than basic table filters. Their unique advantage is the ability to synthesize information from multiple tables simultaneously. For instance, while a search might find a customer's name, a query can link that customer's name to their specific purchase history from a separate table. A well-constructed query uncovers connections that are not immediately visible by simply browsing through data rows.

To build these logical structures, Access utilizes a specialized environment known as Query Design view. This view acts as the blueprint for your data retrieval logic.

Interact with the buttons below to explore the professional tools available in Query Design view.

Query Design View Interactive
+
+
+
+
+
+
+
+

One-table queries

To understand the workflow of query building, we will start with a one-table query. This is essentially an advanced filter that allows for more complex logic than standard datasheet filtering.

Scenario: Imagine our bakery is hosting an event. We need to identify customers who live in Raleigh or within the 27513 zip code. By using two criteria simultaneously, we can generate a targeted mailing list for our invitations.

To create a simple one-table query:

  1. Select the Create tab on the Ribbon and click the Query Design command.
    2. Opening Query Design
  2. In the Show Table dialog, select the Customers table and click Add, then Close.
    3. Adding a table to the query
  3. Double-click the field names you require (e.g., First Name, Last Name, City, Zip Code) to add them to the design grid.
    4. Adding fields to the design grid
  4. Defining Logic:
    • To find records meeting *both* criteria, type them on the same Criteria: row.
    • To find records meeting *either* criteria, type the first in the Criteria: row and the second in the or: row.
    In our case, we type "Raleigh" in the City field and "27513" in the or: row of the Zip Code field.
    5. Defining OR criteria logic
  5. Click the Run command on the Design tab to execute the search.
    6. Executing the query
  6. The results appear in Datasheet view. Save the query via the Quick Access Toolbar to preserve your logic for future use.

Mastering the one-table query is your entry point into data analysis. In our next lesson, we will explore how to connect multiple tables to answer even more complex questions.

Challenge!

Practice your architectural skills by designing the following query in our sample database:

  1. Initiate a new query using the Customers table.
  2. Include the following fields: First Name, Last Name, City, and Zip Code.
  3. Implement the following logic:
    • In the City field, enter "Durham".
    • In the Zip Code field's or: row, enter "27514".
  4. Run the query to verify that the results include anyone who lives in Durham OR in the 27514 zip code.
  5. Save your query with the identifier: Customers who live in Durham.
Next Lesson: Multi-Table Queries →

You May Also Like

Loading...