Lesson 1

In Microsoft Access for Microsoft 365, queries are the main tool you use to ask questions of your data. A single query can pull together fields from several related tables, filter rows you are interested in, perform calculations, and sort results into a readable order. You can then use that query as the data source for forms, reports, and other queries.

This module introduces the process of building queries in modern Access so that you can move confidently between the visual tools (Query Wizards and Query Design view) and the underlying SQL. By the end of the module, you will be able to:

1. Create a query with the Simple Query Wizard.
2. Group and summarize data in a query by using the Simple Query Wizard and totals options.
3. Identify commonly used elements of Query Design view (tables pane, field grid, criteria rows, sort rows).
4. Create a query in Query Design view from one or more related tables.
5. Run a query and view the results in Datasheet view.
6. Save a query design and reopen it later for editing.
7. Use one query as the data source for another (nested queries and subqueries).
8. Edit an existing query to add or remove fields, joins, and criteria.
9. Add criteria to queries to return only the records you need.
10. Use multiple criteria and logical operators (AND, OR, NOT) to refine query results.
11. Specify the sort order for a query on one or more fields.
12. Create calculated fields in a query using expressions (for example, totals, dates, and text concatenation).

Queries are one of the most flexible features in Access. As your database grows and your reporting needs change, you can adjust or build new queries instead of redesigning tables or forms. Here are some of the most common things you can do with queries in Access for Microsoft 365:

1. Choose tables and joins: You can retrieve data from a single table or from many tables that are related by common fields (for example, a CustomerID in both tblCustomers and tblOrders). When using several tables, Access combines matching rows into a single recordset.
2. Choose fields: You decide which fields appear in the result set. For example, you might select the customer name, postal code, order date, and invoice number from tblCustomers and tblOrders instead of showing every column from each table.
3. Provide criteria: Record selection is based on criteria you enter in the design grid. You might limit results to a specific date range, a particular region, or a product category. Criteria can reference values, expressions, or parameters entered by the user at run time.
4. Sort records: You can sort results on one or more fields (for example, last name ascending and then first name ascending), which is especially useful for mailing lists, contact lists, and reports.
5. Perform calculations: Queries can calculate values such as extended prices (Quantity × UnitPrice), totals, averages, counts, minimums and maximums, as well as more complex expressions. Totals queries and GROUP BY clauses allow you to summarize data by customer, product, month, or any other grouping field.
6. Create tables: Make-table queries can create new tables from the results of a query. This is useful when you need a static snapshot of data for archiving, exporting, or testing.
7. Display query data on forms and reports: Forms and reports are often based on queries instead of raw tables. This allows you to present just the fields and rows users need. Each time you open the form or print the report, the query reruns so you see the most current information.
8. Use queries as sources for other queries: You can build queries that use other queries as their data source. This is useful for breaking complex logic into several simpler steps or for ad hoc reporting where you repeatedly refine the result set.
9. Make changes to data in tables: Action queries (UPDATE, APPEND, DELETE, and MAKE TABLE) allow you to modify or move many rows in a single operation. These are powerful tools for maintenance tasks such as updating a price list, archiving records, or cleaning up obsolete data. In Access for Microsoft 365, you are prompted to confirm the changes before an action query runs.

In modern Access, a query is a saved definition of a request for data. The query design is stored in your Access database file (.accdb), but the data it returns may come from:

• Local tables stored in the same Access database.
• Linked tables that point to external sources such as SQL Server, SharePoint lists, or other ODBC data sources.
• Pass-through queries that send SQL directly to an external database engine.

When you run a query, Access translates the design grid into SQL and sends that SQL to the underlying engine. For local Access tables, the Jet/ACE database engine executes the query. For linked SQL Server tables, the query may be partially or completely evaluated on the SQL Server side, especially when you use pass-through queries or well-structured joins and criteria.

Many organizations use Access for Microsoft 365 as a desktop front end while storing their data in SQL Server or Azure SQL Database. In this pattern:

• Access queries appear as familiar objects (just like table-based queries), but they run against linked SQL Server tables.
• You can also create pass-through queries that send T-SQL directly to SQL Server and return the results to Access without additional processing.
• Database professionals may choose to implement complex logic in server-side objects (views, stored procedures), then expose those objects to Access as linked tables or query sources.

For this course, your focus is on designing queries using the Access interface—Query Wizards and Query Design view—while understanding that, in production, those same query designs may be working against local tables, linked SQL Server tables, or a mix of both.