3.1.2 Overview of Access Objects: Queries

In Microsoft Access, queries are powerful objects used to retrieve, manipulate, and analyze data stored in tables. Queries allow you to interact with your data in sophisticated ways, filtering, sorting, and aggregating information to generate meaningful results. Queries can be used to extract specific data, update records, delete records, and even create new tables based on existing data.

What Are Queries in Access?

A query in Access is a request for information from one or more tables or other queries. You can use queries to:

• Extract specific data based on certain criteria.
• Perform calculations or aggregate data.
• Update or delete records in tables.
• Create new tables or modify existing ones.

Queries are typically used to generate reports, forms, or simply to view data in a more structured or filtered format. They provide a way to view and work with data dynamically, without changing the underlying tables.

Types of Queries in Access:

1. Select Queries:
   • Select queries are the most common type of query and are used to retrieve data from one or more tables.
   • A select query can be customized to display specific fields, filter records using criteria, and sort data.
   • Example: Retrieve all customers from the Customers table where the city is "New York".

   SELECT CustomerID, FirstName, LastName, City
   FROM Customers
   WHERE City = 'New York';
   

2. Action Queries: Action queries modify data in your tables. There are four main types of action queries:
   • Update Query: Modifies existing data in a table.
     • Example: Increase the salary of all employees by 10%.
   • Append Query: Adds records from one table to another table.
     • Example: Insert new orders into the Orders table.
   • Delete Query: Removes records from a table based on a condition.
     • Example: Delete customers who have not made any purchases in the last year.
   • Make-Table Query: Creates a new table based on the result of a query.
     • Example: Create a new table with all orders placed by customers in 2023.
3. Crosstab Queries:
   • Crosstab queries summarize data and display it in a matrix format, similar to a pivot table in Excel.
   • This type of query is used for aggregation, where you group data by both rows and columns.
   • Example: Show the total sales by product and month.

   TRANSFORM Sum(Orders.OrderAmount) AS TotalSales
   SELECT ProductName
   FROM Orders
   GROUP BY ProductName
   PIVOT Month(OrderDate);
   

4. Parameter Queries:
   • Parameter queries prompt the user for input when the query is run. This allows for dynamic filtering, where the user can specify criteria at runtime.
   • Example: Prompt the user to enter a city and return all customers from that city.

   SELECT CustomerID, FirstName, LastName
   FROM Customers
   WHERE City = [Enter the city];
   

5. Union Queries:
   • Union queries combine the results of two or more queries into a single result set. The queries must return the same number of columns and compatible data types.
   • Example: Combine customer orders from two different regions.

   SELECT CustomerID, OrderID FROM Orders WHERE Region = 'East'
   UNION
   SELECT CustomerID, OrderID FROM Orders WHERE Region = 'West';
   

6. SQL Queries:
   • SQL queries allow you to write raw SQL (Structured Query Language) to interact with the database. This is useful for advanced users who are familiar with SQL syntax.
   • Access supports SQL-based queries, which can be run directly from the query design view or the SQL View.
   Example: A simple SQL query to find customers from a specific city:

   SELECT CustomerID, FirstName, LastName, City
   FROM Customers
   WHERE City = 'Los Angeles';
   

Creating Queries in Access:

1. Query Design View:
   • In Design View, you can visually design a query by selecting tables, choosing fields, and applying criteria.
   • The query design grid allows you to set:
     • Fields to be included in the query result.
     • Criteria to filter records.
     • Sorting to order the result.
     • Grouping and calculations for summarizing data.
2. Query SQL View:
   • In SQL View, you can write SQL code directly to define the query. This view is for users comfortable with SQL and allows more flexibility in defining queries.
   • Once written, you can execute the SQL query to get the results.
3. Running Queries:
   • Once a query is created, you can run it by switching to Datasheet View or by selecting the Run button.
   • The results will be displayed based on the defined query criteria.
4. Saving Queries:
   • After creating a query, you can save it to reuse it later. Queries can be named and saved for easy access from the database's navigation pane.

Query Criteria and Expressions:

Queries in Access can include criteria to filter data based on specific conditions. Some common ways to filter data include:

• Comparison Operators: Equal to (=), Greater than (>), Less than (<), etc.
• Logical Operators: AND, OR, NOT.
• Wildcards: * (any number of characters), ? (one character) for pattern matching.
• Date Criteria: Between #01/01/2023# AND #12/31/2023#.

Example: Retrieve all orders where the order amount is greater than $100 and the customer is from New York.

SELECT OrderID, OrderAmount, CustomerID
FROM Orders
WHERE OrderAmount > 100 AND CustomerCity = 'New York';

Joins in Queries:

Queries can join multiple tables together using relationships. There are different types of joins used in queries:

1. Inner Join: Returns only matching records from both tables.
2. Left Join (Left Outer Join): Returns all records from the left table, and matching records from the right table. Non-matching records from the right table are returned as null.
3. Right Join (Right Outer Join): Returns all records from the right table, and matching records from the left table.
4. Full Join (Full Outer Join): Returns all records when there is a match in either left or right table. (Note: Access does not support full outer joins directly, but you can simulate it with a combination of left and right joins.)

Example of an inner join to retrieve order details along with customer information:

SELECT Orders.OrderID, Orders.OrderAmount, Customers.FirstName, Customers.LastName
FROM Orders
INNER JOIN Customers
ON Orders.CustomerID = Customers.CustomerID;

Conclusion:

Queries are one of the most powerful features in Microsoft Access. They allow you to extract, manipulate, update, and delete data stored in tables. Through different types of queries, including select queries, action queries, and parameter queries, users can interact with data in a variety of ways to generate reports, perform analysis, and modify the database. Understanding how to create and work with queries is essential for effectively managing and using an Access database.