What Are Access Queries?

In Microsoft Access, a query is the primary tool you use to analyze data. Rather than browsing entire tables, you define a question—“Show me only these rows, with these fields, in this order”—and let Access assemble a result set for you. This result set is dynamic: each time you run the query, it uses the latest data stored in your tables.

When you save a query, you are really saving a query definition (the question) rather than a copy of the data itself. The same query definition can be reused for ad hoc analysis, printed reports, or as the data source behind forms and dashboards.

How Queries Enhance Data Analysis

Queries give you a structured way to explore your data without changing the underlying tables. They help you:

• Focus on just the records you need instead of scanning entire tables.
• Combine related tables so you can see all relevant information in one place.
• Summarize and group data to answer business questions like totals by month or averages by customer.
• Create calculated fields to derive new insight (for example, extended prices or date intervals).
• Reuse the same logic in multiple places, such as reports and forms.

For example, you might start with simple questions like:

1. What is the description for Project 19?
2. Which projects for “Dynamic Solutions” had hours logged in August?

As you gain experience, those questions evolve into richer analyses—trending over time, segmenting by customer type, or identifying exceptions that need attention.

Basic Building Blocks of a Query

A typical select query (the most common type used for analysis) answers four main questions:

1. Which tables or queries? The data sources you want to analyze.
2. Which fields? The columns you want to see in the result set.
3. Which criteria? Conditions that rows must meet to be included.
4. What sort order? How the records should be sorted for review.

In Query Design view, these elements appear in the upper pane (tables and joins) and the lower grid (fields, sort order, and criteria). Behind the scenes, Access represents your design with SQL.

SELECT  Projects.ProjectID,
        Projects.ProjectName,
        Customers.CompanyName,
        Timesheets.WorkDate,
        Timesheets.HoursWorked
FROM    (Projects
         INNER JOIN Timesheets
           ON Projects.ProjectID = Timesheets.ProjectID)
        INNER JOIN Customers
           ON Projects.CustomerID = Customers.CustomerID
WHERE   Customers.CompanyName = "Dynamic Solutions"
ORDER BY Timesheets.WorkDate;

You do not need to write SQL by hand to use queries, but understanding that your design translates into SQL helps you reason about which records are included and why.

Ways Queries Support Better Analysis

Filtering and focusing your data

Criteria rows in Query Design view let you narrow a large dataset down to only the records that matter for a particular question. Examples include:

• Filtering by date range to review the current month or quarter.
• Filtering by status (Open, Closed, On Hold) to see only active work.
• Filtering by region or sales representative for territory analysis.

You can combine criteria with AND and OR logic, use wildcard searches (for example, all customers whose names start with “A”), and parameter prompts that ask the user for a value at run time.

Summarizing with totals and groups

Queries also support aggregate functions—such as SUM, AVG, COUNT, MIN, and MAX—so you can turn detailed records into summary views. For example, you can show:

• Total hours per project.
• Average order value by customer.
• Monthly sales totals by product line.

In Query Design view, you use the Totals button to add a “Total” row and choose how each field should behave (Group By, Sum, Count, and so on).

Crosstab queries for pivot-style analysis

A crosstab query summarizes data in a grid layout that is similar to an Excel PivotTable. For example, you can display project names down the left side and months across the top, with total hours in each cell. This layout makes trends and outliers much easier to spot than a long list of rows.

Crosstab queries are especially helpful when:

• You want to compare values side by side across time periods.
• You want to compare categories (such as department or region) across measures (such as hours or revenue).

Calculated fields and derived insight

Queries allow you to add calculated fields that do not physically exist in any table. These expressions can perform arithmetic, date calculations, or text concatenation. For example:

FullName: [LastName] & ", " & [FirstName]
BillableAmount: [HoursWorked] * [BillingRate]
DaysOpen: Date() - [OpenedDate]

Calculated fields are a key way queries enhance analysis: they let you move from raw storage fields to meaningful business measures without changing table structures.

Reusing query logic in forms and reports

Once you have a query that returns exactly the data you need, you can reuse it:

• As the Record Source for a form that users interact with.
• As the Record Source for a report you print or export.
• As the data source for another query, which further filters or aggregates the results.

This reuse is an important part of good design: you centralize logic in a query definition instead of duplicating it in multiple places.

Other Query Types and When They Matter

Action queries

While this module focuses on queries for analysis, Access also supports action queries that change data:

• UPDATE queries modify existing records.
• APPEND queries add records to a table.
• DELETE queries remove records that meet specific criteria.
• MAKE TABLE queries create a new table from query results.

Action queries are useful for maintenance work and bulk changes, but they should be used carefully—especially in shared databases—because they can affect many rows at once. For analytical workflows, you will typically stay with select and crosstab queries, and use action queries only when you intentionally need to reshape or archive data.

Working with SQL Server and pass-through queries

Many Access applications connect to SQL Server or Azure SQL Database. In these scenarios, some or all of the query processing can be pushed to the server for better performance. You can:

• Build standard Access queries against linked SQL Server tables.
• Create pass-through queries that send T-SQL directly to SQL Server.

When writing T-SQL for a pass-through query, remember that the syntax follows SQL Server rules. For example:

• The wildcard for LIKE is %, not *.
• Text values are enclosed in single quotes, such as 'Dynamic Solutions'.

Being aware of these differences ensures that your analyses remain accurate when they span Access and SQL Server.

Putting It All Together

Whether you are working with a simple desktop database or a front end linked to SQL Server, queries are the key to turning raw tables into meaningful information. By combining tables, filtering and sorting, summarizing with totals and crosstabs, and adding calculated fields, you can answer questions that would be difficult or impossible to solve by looking at individual tables alone.

In the next lessons of this module, you will design and run your own Access queries so you can see how each of these analysis techniques works in practice.