Introduction to queries - Access
Introduction to queries
Access for Microsoft 365 Access 2019 Access 2016 Access 2013 Access 2010 Access 2007
Using a query makes it easier to view, add, delete, or change data in your Access database. Some other reasons for using queries:
Find specific quickly data by filtering on specific criteria (conditions)
Calculate or summarize data
Automate data management tasks, such as reviewing the most current data on a recurring basis.
Note: If you want to try out the queries in the examples, use an Access desktop database.
Queries help you find and work with your data
In a well-designed database, the data that you want to present through a form or report is usually located in multiple tables. A query can pull the information from various tables and assemble it for display in the form or report. A query can either be a request for data results from your database or for action on the data, or for both. A query can give you an answer to a simple question, perform calculations, combine data from different tables, add, change, or delete data from a database. Since queries are so versatile, there are many types of queries and you would create a type of query based on the task.
Major query types
Use
Select
To retrieve data from a table or make calculations.
Action
Add, change, or delete data. Each task has a specific type of action query. Action queries are not available in Access web apps.
Create a select query
If you want to review data from only certain fields in a table, or review data from multiple tables simultaneously or maybe just see the data based on certain criteria, a select query type would be your choice. For more information, see create a simple select query.
Review data from select fields
For example, if your database has a table with a lot of information about products and you want to review a list of products and their prices, here’s how you’d create a select query to return just the product names and the respective price:
Open the database and on the Create tab, click Query Design.
On the Tables tab, double-click the Products table.
In the Products table, let’s say that you have Product Name and List Price fields. Double-click the Product Name and List Price to add these fields to the query design grid.
On the Design tab, click Run. The query runs, and displays a list of products and their prices.
Review data from multiple related tables simultaneously
For example, if you have a database for a store that sells food items and you want to review orders for customers who live in a particular city. Say that the data about orders and data about customers are stored in two tables named Customers and Orders respectively. If each table has a Customer ID field, which forms the basis of a one-to-many relationship between the two tables. You can create a query that returns orders for customers in a particular city, for example, Las Vegas, by using the following procedure:
Open the database. On the Create tab, in the Query group, click Query Design.
On the Tables tab, double-click Customers and Orders.
Note the line (called a join) that connects the ID field in the Customers table and the Customer ID field in the Orders table. This line shows the relationship between the two tables.
In the Customers table, double-click Company and City to add these fields to the query design grid.
In the query design grid, in the City column, clear the check box in the Show row.
In the Criteria row of the City column, type Las Vegas.
Clearing the Show check box prevents the query from displaying the city in its results, and typing Las Vegas in the Criteria row specifies that you want to see only records where the value of the City field is Las Vegas. In this case, the query returns only the customers that are located in Las Vegas. You don’t need to display a field to use it with a criterion.
In the Orders table, double-click Order ID and Order Date to add these fields to the next two columns of the query design grid.
On the Design tab, in the Results group, click Run. The query runs, and then displays a list of orders for customers in Las Vegas.
Press CTRL+S to save the query.
Create a parameter query
If you frequently want to run variations of a particular query, consider using a parameter query. When you run a parameter query, the query prompts you for field values, and then uses the values that you supply to create criteria for your query.
Note: You cannot create a parameter query in an Access web app.
Continuing from the previous example where you learnt to create a select query that returns orders for customers located in Las Vegas, you can modify the select query to prompt you to specify the city each time that you run the query. To follow along, open the database that you created in the previous example:
In the Navigation Pane, right-click the query named Orders by City (that you created in the previous section), and then click Design View on the shortcut menu.
In the query design grid, in the Criteria row of the City column, delete Las Vegas, and then type [For what city?].
The string [For what city?] is your parameter prompt. The square brackets indicate that you want the query to ask for input, and the text (in this case, For what city?) is the question that the parameter prompt displays.
Note: Neither a period (.) nor an exclamation point (!) can be used as text in a parameter prompt.
Select the check box in the Show row of the City column, so that the query results will display the city.
On the Design tab, in the Results group, click Run. The query prompts you to enter a value for City.
Type New York, and then press ENTER to see orders for customers in New York.
What if you don't know what values you can specify? You can use wildcard characters as part of the prompt:
On the Home tab, in the Views group, click View, and then click Design View.
In the query design grid, in the Criteria row of the City column, type Like [For what city?]&"*".
In this parameter prompt, the Like keyword, the ampersand (&), and the asterisk (*) enclosed in quotation marks allow the user to type a combination of characters, including wildcard characters, to return a variety of results. For example, if the user types *, the query returns all cities; if the user types L, the query returns all cities that start with the letter "L;" and if the user types *s*, the query returns all cities that contain the letter "s."
On the Design tab, in the Results group, click Run, and at the query prompt, type New, and press ENTER.
The query runs, and then displays orders for customers in New York.
Specify parameter data types
You can also specify what type of data a parameter should accept. You can set the data type for any parameter, but it is especially important to set the data type for numeric, currency, or date/time data. When you specify the data type that a parameter should accept, users see a more helpful error message if they enter the wrong type of data, such as entering text when currency is expected.
If a parameter is set to accept text data, any input is interpreted as text, and no error message is displayed.
To specify the data type for parameters in a query, use the following procedure:
With the query open in Design view, on the Design tab, in the Show/Hide group, click Parameters.
In the Query Parameters dialog box, in the Parameter column, type the prompt for each parameter for which you want to specify the data type. Make sure that each parameter matches the prompt that you use in the Criteria row of the query design grid.
In the Data Type column, select the data type for each parameter.
For more information, see using parameters to ask for input when running a query.
Create a totals query
The Total row in a datasheet is very useful, but for more complex questions, you use a totals query. A totals query is a select query that allows you to group and summarize data, like when you want to see total sales per product. In a totals query, you can use the Sum function (an aggregate function), to see total sales per product.
Note: You cannot use aggregate functions in an Access web app.
Use the following procedure to modify the Product Subtotals query that you created in the previous example so that it summarizes product subtotals by product.
On the Home tab, click View > Design View.
The Product Subtotals query opens in Design view.
On the Design tab, in the Show/Hide group, click Totals.
The Totals row is displayed in the query design grid.
Note: Although they have similar names, the Totals row in the design grid and the Total row in a datasheet are not the same:
You can group by field values by using the Totals row in the design grid.
You can add a datasheet Total row to the results of a totals query.
When you use the Totals row in the design grid, you must choose an aggregate function for each field. If you do not want to perform a calculation on a field, you can group by the field.
In the second column of the design grid, in the Total row, select Sum from the drop-down list.
On the Design tab, in the Results group, click Run. The query runs, and then displays a list of products with subtotals.
Press CTRL+S to save the query. Leave the query open.
For more information, see Display column totals in a datasheet using a Totals row.
Make calculations based on your data
You usually would not use tables to store calculated values, like subtotals, even if they are based on data in the same database, because calculated values can become outdated if the values that they are based on changes. For example, you would not store someone's age in a table, because every year you would have to update the value; instead, you store the person's date of birth, and then use a query to calculate the person's age.
For example if you have a database for some products you’d like to sell. This database has a table called Orders Details that has information about the products in fields such as, price of each product and the quantities. You can calculate the subtotal by using a query that multiplies the quantity of each product by the unit price for that product, multiplies the quantity of each product by the unit price and discount for that product, and then subtracts the total discount from the total unit price. If you created the sample database in the previous example, open it and follow along:
On the Create tab, click Query Design.
On the Tables tab, double-click Order Details.
In the Order Details table, double-click Product ID to add this field to the first column of the query design grid.
In the second column of the grid, right-click the Field row, and then click Zoom on the shortcut menu.
In the Zoom box, type or paste the following: Subtotal: ([Quantity]*[Unit Price])-([Quantity]*[Unit Price]*[Discount])
Click OK.
On the Design tab, click Run. The query runs, and then displays a list of products and subtotals, per order.
Press CTRL+S to save the query, and then name the query Product Subtotals.
For more information, see Display column totals in a datasheet using a Totals row.
Display summarized or aggregate data
When you use tables to record transactions or store regularly occurring numeric data, it is useful to be able to review that data in aggregate, such as sums or averages. In Access, you can add a Totals row to a datasheet. Total row is a row at the bottom of the datasheet that can display a running total or other aggregate value.
Run the Product Subtotals query you created earlier, and leave the results open in Datasheet view.
On the Home tab, click Totals. A new row appears at the bottom of the datasheet, with the word Total in the first column.
Click the cell in the last row of the datasheet named Total.
Click the arrow to view the available aggregate functions. Because the column contains text data, there are only two choices: None and Count.
Select Count. The content of the cell changes from Total to a count of the column values.
Click the adjoining cell (the second column). Note that an arrow appears in the cell.
Click the arrow, and then click Sum. The field displays a sum of the column values.
Leave the query open in Datasheet view.
Create a crosstab query
Now suppose that you want to review product subtotals, but you also want to aggregate by month, so that each row shows subtotals for a product, and each column shows product subtotals for a month. To show subtotals for a product and to show product subtotals for a month, use a crosstab query.
Note: A crosstab query cannot be displayed in an Access web app.
You can modify the Product Subtotals query again so that the query returns rows of product subtotals and columns of monthly subtotals.
On the Home tab, in the Views group, click View, and then click Design View.
In the Query Setup group, click Add Tables (or Show Table in Access 2013).
Double-click Orders, and then click Close.
On the Design tab, in the Query Type group, click Crosstab. In the design grid, the Show row is hidden, and the Crosstab row is displayed.
In the third column of the design grid, right-click the Field row, and then click Zoom on the shortcut menu. The Zoom box opens.
In the Zoom box, type or paste the following: Month: "Month " & DatePart("m", [Order Date])
Click OK.
In the Crosstab row, select the following values from the drop-down list: Row Heading for the first column, Value for the second column, and Column Heading for the third column.
On the Design tab, in the Results group, click Run. The query runs, and then displays product subtotals, aggregated by month.
Press CTRL+S to save the query.
For more information about crosstab queries, see Make summary data easier to read by using a crosstab query.
Create a make table query
You can use a make-table query to create a new table from data that is stored in other tables.
Note: A make-table query is not available in Access web apps.
For example, suppose that you want to send data for Chicago orders to a Chicago business partner who uses Access to prepare reports. Instead of sending all your order data, you want to restrict the data that you send to data specific to Chicago orders.
You can build a select query that contains Chicago order data, and then use the select query to create the new table by using the following procedure:
Open the example database from the previous example.
To run a make-table query, you may need to enable the database content.
Note: If you see a message beneath the Ribbon about enabling the database, click Enable content.If your database is already in a trusted location, you will not see the Message Bar.
On the Create tab, in the Query group, click Query Design.
Double-click Order Details and Orders.
In the Orders table, double-click Customer ID and Ship City to add these fields to the design grid.
In the Order Details table, double-click Order ID, Product ID, Quantity, Unit Price, and Discount to add these fields to the design grid.
In the Ship City column of the design grid, clear the box in the Show row. In the Criteria row, type 'Chicago' (include the single quotation marks). Verify the query results before you use them to create the table.
On the Design tab, in the Results group, click Run.
Press Ctrl + S to save the query.
In the Query Name box, type Chicago Orders Query, and then click OK.
On the Home tab, in the Views group, click View, and then click Design View.
On the Design tab, in the Query Type group, click Make Table.
In the Make Table dialog box, in the Table Name box, type Chicago Orders, and then click OK.
On the Design tab, in the Results group, click Run.
In the confirmation dialog box, click Yes, and see the new table displayed in the Navigation Pane.
Note: If there is already a table with the same name that you specified, Access deletes that table before running the query.
For more information about using make table queries, see Create a make table query.
Create an append query
You can use an append query to retrieve data from one or more tables and add that data to another table.
Note: Append query is not available in Access web apps.
For example, suppose that you created a table to share with a Chicago business associate, but you realize that the associate also works with clients in the Milwaukee area. You want to add rows that contain Milwaukee area data to the table before you share the table with your associate. You can add Milwaukee area data to the Chicago Orders table by using the following procedure:
Open the query named "Chicago Orders Query" you created earlier in Design view.
On the Design tab, in the Query Type group, click Append. The Append dialog box opens.
In the Append dialog box, click the arrow in the Table Name box, select Chicago Orders from the drop-down list, and then click OK.
In the design grid, in the Criteria row of the Ship City column, delete 'Chicago', and then type 'Milwaukee'.
In the Append To row, select the appropriate field for each column.
In this example, the Append To row values should match the Field row values, but that is not required for append queries to work.
On the Design tab, in the Results group, click Run.
Note: While running a query that returns a large amount of data you might get an error message indicating that you will not be able to undo the query. Try increasing the limit on the memory segment to 3MB to allow the query to go through.
For more information about append queries, see Add records to a table by using an append query.
Create an update query
You can use an update query to change the data in your tables, and you can use an update query to enter criteria to specify which rows should be updated. An update query provides you an opportunity to review the updated data before you perform the update.
Important: An action query cannot be undone. You should consider making a backup of any tables that you will update by using an update query. An update query is not available in Access web apps.
In the previous example, you appended rows to the Chicago Orders table. In the Chicago Orders table, the Product ID field shows the numeric Product ID. To make the data more useful in reports, you can replace the product IDs with product names, use the following procedure:
Open the Chicago Orders table in Design view.
In the Product ID row, change the Data Type from Number to Text.
Save and close the Chicago Orders table.
On the Create tab, in the Query group, click Query Design.
Double-click Chicago Orders and Products.
On the Design tab, in the Query Type group, click Update.
In the design grid, the Sort and Show rows disappear, and the Update To row appears.
In the Chicago Orders table, double-click Product ID to add this field to the design grid.
In the design grid, in the Update To row of the Product ID column, type or paste the following: [Products].[Product Name]
Tip: You can use an update query to delete field values by using an empty string ("") or NULL in the Update To row.
In the Criteria row, type or paste the following: [Product ID] Like ([Products].[ID])
You can review which values will be changed by an update query by viewing the query in Datasheet view.
On the Design tab, click View > Datasheet View. The query returns a list of Product IDs that will be updated.
On the Design tab, click Run.
When you open the Chicago Orders table, you will see that the numeric values in the Product ID field have been replaced by the product names from the Products table.
For more information about update queries, see Create and run an update query.
Create a delete query
You can use a delete query to delete data from your tables, and you can use a delete query to enter criteria to specify which rows should be deleted. A delete query provides you an opportunity to review the rows that will be deleted before you perform the deletion.
Note: A delete query option is not available in Access web apps.
For example, say that while you were preparing to send the Chicago Orders table from the previous example, to your Chicago business associate, you notice that some of the rows contain a number of empty fields. You decided to remove these rows before you send the table. You could just open the table and delete the rows manually, but if you have many rows to delete and you have clear criteria for which rows should be deleted, you might find it helpful to use a delete query.
You can use a query to delete rows in the Chicago Orders table that do not have a value for Order ID by using the following procedure:
On the Create tab, click Query Design.
Double-click Chicago Orders.
On the Design tab, in the Query Type group, click Delete. In the design grid, the Sort and Show rows disappear, and the Delete row appears.
In the Chicago Orders table, double-click Order ID to add it to the grid.
In the design grid, in the Criteria row of the Order ID column, type Is Null.
On the Design tab, in the Results group, click Run.
