Refining Queries  «Prev  Next»
Lesson 1

Refining Access Queries

Queries are an intricate part of Access, and they allow you to get just about any view of your data you need, as well as handle operations such as adding, deleting, and updating multiple records at a time. The real trick to using queries effectively is to make sure you use the correct query for the job you need to perform. For instance, if you use an Update query when you are trying to delete information, then the query will not perform the task at hand. This module is about showing you where and how to use the various types of queries for tasks. By the end of this module, you will know how to:
  1. Use Filter by Selection and Filter by Form while in a query’s datasheet
  2. Use a parameter with your query to specify criteria dynamically
  3. Use multiple criteria in a query using the AND and OR operators
  4. Set properties for fields in queries
  5. Identify fields you can index to improve query performance
  6. Identify the three different types of joins available and how they can be used
  7. Discuss the different types of queries and where you would use them
  8. Create action queries to perform bulk operations
  9. Work with a Totals query to present group totals
  10. Explain what Crosstab queries are and how to create them

Queries are Fundamental to Microsoft Access

In Microsoft Access, queries are a central part of data processing and application logic. Their importance can be ranked based on how critical they are to retrieving, updating, analyzing, or transforming data. Here’s a prioritized ranking with descriptions:
  1. Select Queries (Most Important)
    • Purpose: Retrieve and display data from one or more tables.
    • Why important: They are the foundation for forms, reports, and other queries. Without Select queries, you cannot view or analyze data meaningfully.
    • Common use: Filtering, sorting, joining tables, calculating totals, etc.
  2. Action Queries

    These modify data and are essential for automating updates.

    • a. Update Queries
      • Purpose: Change existing records.
      • Use case: Changing a tax rate across all customer invoices.
    • b. Append Queries
      • Purpose: Add new records to a table.
      • Use case: Importing new customer orders from a temporary table.
    • c. Delete Queries
      • Purpose: Remove records from a table.
      • Use case: Cleaning out old logs beyond a retention period.
    • d. Make-Table Queries
      • Purpose: Create a new table from the results of a query.
      • Use case: Archiving monthly sales data.
    Why second in importance: Action queries allow dynamic changes to the database—powerful, but riskier without safeguards.
  3. Crosstab Queries
    • Purpose: Aggregate data into a pivot table-like format.
    • Use case: Summarizing total sales by region and month.
    • Importance: Critical for reporting and dashboard-like views.
  4. Parameter Queries
    • Purpose: Prompt the user for input at runtime.
    • Use case: "Enter Start Date" to filter invoice results.
    • Importance: Increases interactivity and flexibility for end users.
  5. SQL Queries (Pass-Through / Union)
    • a. Pass-Through Queries
      • Purpose: Send SQL directly to the backend (e.g., SQL Server).
      • Importance: Useful in enterprise scenarios, bypassing Jet/ACE engine.
    • b. Union Queries
      • Purpose: Combine results from multiple Select queries.
      • Importance: Useful when combining similar datasets (e.g., 2023 and 2024 orders).
  6. Data Definition Queries (DDL)
    • Purpose: Create, alter, or drop tables and constraints.
    • Importance: Rarely used in Access but critical for advanced automation or backend schema changes.

Summary Table
Rank Query Type Key Use Risk Level Typical Users
1 Select Data retrieval, joining, filtering Low All users
2 Action Modify data (Update, Append, etc.) Medium-High Power users, developers
3 Crosstab Summary reports and pivot views Low Analysts, report users
4 Parameter Interactive filtering Low All users
5 SQL (Pass-through, Union) Complex logic, external DB interaction Medium Advanced users
6 Data Definition (DDL) Schema-level changes High Developers
Diagram which shows how these query types relate to tables, forms, and reports
Diagram which shows how these query types relate to tables, forms, and reports

Select Query

A select query is a type of database object that shows information in Datasheet view. A query can get its data from one or more tables, from existing queries, or from a combination of the two. The tables or queries from which a query gets its data are referred to as its recordsource.
Whether you create simple select queries by using a wizard or by working in Design view, the steps are essentially the same. You choose the recordsource that you want to use and the fields that you want to include in the query, and, optionally, you specify criteria to refine the results.
After you have created a select query, you run it to see the results. Running a select query is simple , you just open it in Datasheet view. You can then reuse it whenever you need, for example, as a recordsource for a form, report, or another query.
In the next lesson, you will learn how to use Filter by Selection and Filter by Form while in a query's datasheet.

SEMrush Software TargetSEMrush Software Banner