Microsoft Access SQL Queries: The Complete Reference Guide

Introduction: Understanding Access Queries

Microsoft Access queries are powerful tools that allow you to retrieve, analyze, manipulate, and summarize data from one or more tables in your database. They use Structured Query Language (SQL) to define what data you want to work with and how you want to process it.

Why Access Queries Matter:

• Retrieve precisely the data you need from complex databases
• Create dynamic views that update as underlying data changes
• Perform calculations and aggregations on your data
• Modify data across multiple records simultaneously
• Build the foundation for forms, reports, and other database objects
• Filter and sort data without changing the original tables
• Combine data from multiple related tables

Core SQL Query Types in Access

Select Queries

Used to retrieve data from one or more tables

Basic Syntax:

SELECT [fields]
FROM [table(s)]
[WHERE condition]
[ORDER BY fields];

Example:

SELECT FirstName, LastName, City
FROM Customers
WHERE State = 'CA'
ORDER BY LastName;

Action Queries

Specialized Queries

Building Effective SELECT Queries

Field Selection

All Fields:

SELECT *
FROM Customers;

Specific Fields:

SELECT CustomerID, FirstName, LastName
FROM Customers;

Calculated Fields:

SELECT OrderID, Quantity * UnitPrice AS ExtendedPrice
FROM OrderDetails;

Field Aliases:

SELECT FirstName & " " & LastName AS FullName
FROM Employees;

Unique Values:

SELECT DISTINCT City
FROM Customers;

Working with Multiple Tables

Inner Join:

SELECT Orders.OrderID, Customers.CustomerName
FROM Orders INNER JOIN Customers
ON Orders.CustomerID = Customers.CustomerID;

Left Outer Join:

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers LEFT JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;

Right Outer Join:

SELECT Orders.OrderID, Employees.LastName
FROM Orders RIGHT JOIN Employees
ON Orders.EmployeeID = Employees.EmployeeID;

Self Join:

SELECT E1.LastName AS Employee, E2.LastName AS Manager
FROM Employees AS E1 INNER JOIN Employees AS E2
ON E1.ManagerID = E2.EmployeeID;

Multiple Joins:

SELECT Orders.OrderID, Customers.CustomerName, Employees.LastName
FROM (Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID)
INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID;

Filtering Records with WHERE

Comparison Operators:

Logical Operators:

Special Operators:

Using Wildcards with LIKE:

Sorting Results with ORDER BY

Basic Sorting:

SELECT ProductName, UnitPrice
FROM Products
ORDER BY UnitPrice;

Multiple Sort Fields:

SELECT LastName, FirstName
FROM Employees
ORDER BY LastName, FirstName;

Descending Order:

SELECT ProductName, UnitPrice
FROM Products
ORDER BY UnitPrice DESC;

Mixed Direction:

SELECT LastName, FirstName
FROM Employees
ORDER BY LastName ASC, HireDate DESC;

Grouping and Aggregating Data

Aggregate Functions:

Basic Grouping:

SELECT Category, COUNT(*) AS ProductCount
FROM Products
GROUP BY Category;

Multiple Group Fields:

SELECT Category, SupplierID, COUNT(*) AS ProductCount
FROM Products
GROUP BY Category, SupplierID;

Filtering Groups:

SELECT Category, COUNT(*) AS ProductCount
FROM Products
GROUP BY Category
HAVING COUNT(*) > 5;

Filtering Records and Groups:

SELECT Category, COUNT(*) AS ProductCount
FROM Products
WHERE Discontinued = False
GROUP BY Category
HAVING COUNT(*) > 5;

Advanced Query Techniques

Subqueries

In WHERE Clause:

SELECT ProductName, UnitPrice
FROM Products
WHERE UnitPrice > (SELECT AVG(UnitPrice) FROM Products);

In FROM Clause:

SELECT Avg(OrderCount) AS AvgOrdersPerCustomer
FROM (SELECT CustomerID, COUNT(*) AS OrderCount 
      FROM Orders 
      GROUP BY CustomerID) AS CustomerOrders;

In SELECT Clause:

SELECT CustomerID, 
       (SELECT COUNT(*) FROM Orders WHERE Orders.CustomerID = Customers.CustomerID) AS OrderCount
FROM Customers;

Using UNION to Combine Results

Basic UNION:

SELECT 'Customer' AS Type, FirstName, LastName, Phone
FROM Customers
UNION
SELECT 'Employee' AS Type, FirstName, LastName, Phone
FROM Employees
ORDER BY LastName, FirstName;

UNION vs. UNION ALL:

• UNION – Removes duplicate rows
• UNION ALL – Keeps all rows (faster)

SELECT City FROM Customers
UNION ALL
SELECT City FROM Suppliers
ORDER BY City;

Creating Crosstab Queries

Basic Crosstab:

TRANSFORM Sum(Orders.Amount)
SELECT Customers.CustomerName
FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
GROUP BY Customers.CustomerName
PIVOT Orders.OrderMonth;

Dynamic Pivot Values:

TRANSFORM Sum(Sales.Amount)
SELECT Products.ProductName
FROM Products INNER JOIN Sales ON Products.ProductID = Sales.ProductID
GROUP BY Products.ProductName
PIVOT Format(Sales.SaleDate, "mmm-yyyy");

Working with Dates

Date Functions:

Common Date Queries:

-- Orders from current month
SELECT * FROM Orders
WHERE Year(OrderDate) = Year(Date()) AND Month(OrderDate) = Month(Date());

-- Orders in last 30 days
SELECT * FROM Orders
WHERE DateDiff("d", OrderDate, Date()) <= 30;

-- Group by year
SELECT Year(OrderDate) AS OrderYear, COUNT(*) AS OrderCount
FROM Orders
GROUP BY Year(OrderDate);

-- Group by month
SELECT Year(OrderDate) AS OrderYear, 
       Month(OrderDate) AS OrderMonth, 
       COUNT(*) AS OrderCount
FROM Orders
GROUP BY Year(OrderDate), Month(OrderDate);

Parameter Queries

Single Parameter:

SELECT * FROM Customers
WHERE State = [Enter state:];

Multiple Parameters:

SELECT * FROM Orders
WHERE OrderDate BETWEEN [Enter start date:] AND [Enter end date:];

Default Values:

SELECT * FROM Products
WHERE UnitPrice > [Minimum price? (Default=50)];

Numeric Parameters:

PARAMETERS [Minimum Price] Currency;
SELECT * FROM Products
WHERE UnitPrice > [Minimum Price];

Action Queries in Detail

Append Queries

Basic Append:

INSERT INTO Customers (FirstName, LastName, Email)
VALUES ('John', 'Smith', 'john@example.com');

Append from Query:

INSERT INTO ArchivedOrders
SELECT * FROM Orders
WHERE OrderDate < #1/1/2022#;

Append Selected Fields:

INSERT INTO CustomerSummary (CustomerID, FullName, OrderCount)
SELECT Customers.CustomerID, 
       FirstName & " " & LastName AS FullName,
       Count(Orders.OrderID)
FROM Customers LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
GROUP BY Customers.CustomerID, FirstName & " " & LastName;

Update Queries

Basic Update:

UPDATE Products
SET UnitPrice = UnitPrice * 1.1
WHERE CategoryID = 5;

Update with Join:

UPDATE Orders INNER JOIN Customers 
  ON Orders.CustomerID = Customers.CustomerID
SET Orders.ShippingAddress = Customers.Address, 
    Orders.ShippingCity = Customers.City,
    Orders.ShippingState = Customers.State
WHERE Orders.ShippingAddress IS NULL;

Conditional Update:

UPDATE Inventory
SET ReorderLevel = 
    CASE 
        WHEN SalesVolume > 1000 THEN 100
        WHEN SalesVolume > 500 THEN 50
        ELSE 25
    END;

Delete Queries

Basic Delete:

DELETE FROM Orders
WHERE OrderDate < #1/1/2020#;

Delete with Join:

DELETE Orders.*
FROM Orders INNER JOIN Customers 
  ON Orders.CustomerID = Customers.CustomerID
WHERE Customers.Status = 'Inactive';

Delete All Records:

DELETE FROM OrderDetails;

Make-Table Queries

Create from Single Table:

SELECT * INTO ArchivedOrders
FROM Orders
WHERE OrderDate < #1/1/2022#;

Create from Join:

SELECT Customers.CustomerID, 
       Customers.CustomerName,
       Count(Orders.OrderID) AS OrderCount, 
       Sum(Orders.OrderAmount) AS TotalSales
INTO CustomerSummary
FROM Customers INNER JOIN Orders 
  ON Customers.CustomerID = Orders.CustomerID
GROUP BY Customers.CustomerID, Customers.CustomerName;

Create Table Structure Only:

SELECT * INTO NewCustomers
FROM Customers
WHERE 1=0;

Optimizing Query Performance

Query Optimization Techniques

1. Use Indexes Wisely

   • Create indexes on fields used in joins, WHERE clauses, and ORDER BY
   • Avoid indexing fields with few unique values
   • Consider composite indexes for multi-field conditions

2. Minimize Resource Usage

   • Select only needed fields (avoid SELECT *)
   • Filter early in the query (WHERE before GROUP BY)
   • Use EXISTS instead of IN for subqueries when appropriate

3. Join Optimization

   • Join tables in order from smallest to largest result sets
   • Use inner joins instead of outer joins when possible
   • Ensure join fields are indexed

4. Expression Optimization

   • Move expressions to the “right side” of comparisons
   • Avoid functions on indexed fields in WHERE clauses
   • Use simple comparison operators when possible

Common Performance Issues

Troubleshooting Access Queries

Common Error Messages

Debugging Techniques

Step 1: Test SQL Parts

• Break complex queries into simpler pieces
• Test individual conditions and joins
• Use SELECT COUNT(*) to verify record counts

Step 2: Check Data Types

• Ensure compared fields have compatible types
• Watch for implicit conversions
• Use conversion functions when needed

Step 3: Verify Relationships

• Check that join fields have matching types
• Ensure relationships are properly defined
• Consider referential integrity constraints

Step 4: Temporary Queries

• Create intermediate queries for complex operations
• Use make-table queries for troubleshooting
• Build queries incrementally, verifying each step

Resources for Further Learning

Microsoft Documentation

• Access SQL reference
• Access query design documentation
• Microsoft Access forum

Books

• Microsoft Access 2019 Programming By Example with VBA, XML, and ASP by Julitta Korol
• Alison Balter’s Mastering Microsoft Access series
• Access 2019 Bible by Michael Alexander and Richard Kusleika

Online Courses

• Microsoft Learn Access courses
• LinkedIn Learning (formerly Lynda.com) Access courses
• Udemy Access database courses

Community Resources

• StackOverflow Access questions
• AccessForums.net
• DatabaseDevelopers.com Access forum
• GitHub sample Access projects

Final Reminders & Best Practices

• Document your queries with comments and descriptive names
• Back up your database before running action queries
• Test complex queries on a subset of data first
• Consider splitting databases into front-end and back-end for multi-user environments
• Normalize your data to reduce redundancy and improve query performance
• Use appropriate data types for fields to optimize storage and performance
• Implement error handling in applications that use queries
• Regularly compact and repair your database to maintain performance
• Review and update queries when data structure changes
• Consider query security implications in shared environments