Introduction to queries

Access for Microsoft 365 Access 2019 Access 2016 Access 2013 Access 2010 Access 2007

Employing a query simplifies your ability 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 summarise 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

Create a select query

Create a parameter query

Create a totals query

Create a crosstab query

Create a make table query

Create an append query

Create an update query

Create a delete query

Queries help you find and work with your data

In a effectively designed database, the data that you want to present through a form or report is normally found in numerous tables. A query can extract the information from multiple tables and unite 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 typesUse
SelectTo retrieve data from a table or make calculations.
ActionAdd, 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:

  • Enter the database and on the Create tab, click Query Design.
  • In the Show Table box, on the Tables tab, double-click the Products table and then exit the dialogue box.
  • 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 append these fields to the query design grid.
  • On the Design tab, pick Run. The query runs, and presents a list of products and their prices.

Top of Page

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:

  • Access the database. On the Create tab, in the Query group, pick Query Design.

  • In the Show Table dialogue box, on the Tables tab, double-click Customers and Orders.
  • Exit the Show Table dialogue box. Note the line (called a join) that links the ID field in the Customers table and the Customer ID field in the Orders table. This line depicts the relationship between the two tables.

  • In the Customers table, double-click Company and City to create these fields to the query design grid.

  • In the query design grid, in the City column, unmark the checkbox in the Show row.
  • In the Criteria row of the City column, enter Las Vegas. Clearing the Show checkbox stops the query from showing the city in its results, and writing 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 yields only the customers that are situated 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 input these fields to the next two columns of the query design grid.
  • On the Design tab, in the Results group, pick Run. The query runs, and then displays a list of orders for customers in Las Vegas.
  • Press CTRL+S to save the query.

Top of Page

Create a parameter query

If you regularly want to run variations of a particular query, consider using a parameter query. Once you run a parameter query, the query prompts you for field values, and then uses the values that you provide 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 called Orders by City (that you created in the previous section), and then select Design View on the shortcut menu.

  • In the query design grid, in the Criteria row of the City column, remove Las Vegas, and then enter [For what city?]. The string [For what city?] is your parameter prompt. The square brackets reflect 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.

  • Choose the checkbox 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, choose Run. The query prompts you to state a value for City.
  • Put 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 utilise wildcard characters as part of the prompt:
  • On the Home tab, in the Views group, pick View, and then click Design View. In the query design grid, in the Criteria row of the City column, input Like [For what city?]&”*”. In this parameter prompt, the Like keyword, the ampersand (&), and the asterisk (*) wrapped in quotation marks enable the user to write a combination of characters, including wildcard characters, to capture a plethora 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, select Run, and at the query prompt, write New, and press ENTER.The query runs, and then showcases orders for customers in New York.

Specify parameter data types

You can also elaborate what type of data a parameter should accept. You can set the data type for any parameter, but it is critically vital to set the data type for numeric, currency, or date/time data. Once you specify the data type that a parameter should accept, users notice a more useful helpful error message if they state 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, pick Parameters.
  • In the Query Parameters dialogue box, in the Parameter column, state the prompt for each parameter for which you want to specify the data type. Check that each parameter matches the prompt that you use in the Criteria row of the query design grid.
  • In the Data Type column, choose the data type for each parameter.

For more information, see using parameters to ask for input when running a query.

Top of Page

Create a totals query

The Total row in a datasheet is a valuable function, but for more complex questions, you use a totals query. A totals query is a select query that enables you to group and summarise 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 view 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, press View > Design View.The Product Subtotals query opens in Design view.
  • On the Design tab, in the Show/Hide group, pick 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.

Top of Page

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, pick Query Design.
  • In the Show Table dialogue box, on the Tables tab, double-click Order Details.
  • Close the Show Table dialogue box.
  • In the Order Details table, double-click Product ID to append 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 press Zoom on the shortcut menu.
  • In the Zoom box, type or paste the following: Subtotal: ([Quantity]*[Unit Price])-([Quantity]*[Unit Price]*[Discount])
  • Press OK.
  • On the Design tab, select Run. The query runs, and then displays a list of products and subtotals, per order.
  • Press CTRL+S to save the query, and then call the query Product Subtotals.

For more information, see Display column totals in a datasheet using a Totals row.

Top of Page

Display summarised 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 produced earlier, and leave the results open in Datasheet view.
  • On the Home tab, select Totals. A new row appears at the bottom of the datasheet, with the word Total in the first column.
  • Choose the cell in the last row of the datasheet named Total.
  • Pick the arrow to view the available aggregate functions. Because the column contains text data, there are only two choices: None and Count.
  • Click Count. The content of the cell changes from Total to a count of the column values.
  • Select the adjoining cell (the second column). Note that an arrow appears in the cell.
  • Press the arrow, and then choose Sum. The field displays a sum of the column values.
  • Keep the query open in Datasheet view.

Top of Page

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, pick View, and then choose Design View.
  • In the Query Setup group, choose Show Table.
  • In the Show Table dialogue box, double-click Orders, and then choose Close.
  • On the Design tab, in the Query Type group, choose Crosstab. In the design grid, the Show row is hidden, and the Crosstab row is shown.
  • In the third column of the design grid, right-click the Field row, and then choose Zoom on the shortcut menu. The Zoom box opens.
  • In the Zoom box, state or paste the following: Month: “Month ” & DatePart(“m”, [Order Date])
  • Press OK.
  • In the Crosstab row, choose 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, choose Run. The query runs, and then presents product subtotals, aggregated by month.
  • Press CTRL+S to save the query. For further information about crosstab queries, see Make summary data easier to read by using a crosstab query.

Top of Page

Create a make table query

You can use a make-table query to design 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 construct a select query that includes Chicago order data, and then use the select query to produce the new table by using the following procedure:

  • Access the example database from the previous example. To run a make-table query, you might 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, choose Query Design.
  • In the Show Table dialogue box, double-click Order Details and Orders and Close the Show Table dialogue box.
  • In the Orders table, double-click Customer ID and Ship City to embed these fields to the design grid.
  • In the Order Details table, double-click Order IDProduct IDQuantityUnit Price, and Discount to apply these fields to the design grid.
  • In the Ship City column of the design grid, unmark 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, choose Run.
  • Press Ctrl + S to save the query.
  • In the Query Name box, enter Chicago Orders Query, and then pick OK.
  • On the Home tab, in the Views group, choose View, and then press Design View.
  • On the Design tab, in the Query Type group, choose Make Table.
  • In the Make Table dialogue box, in the Table Name box, state Chicago Orders, and then click OK.
  • On the Design tab, in the Results group, choose Run.
  • In the confirmation dialogue box, choose Yes, and see the new table presented 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.

Top of Page

Create an append query

You can use an append query to retrieve data from one or more tables and import 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 realise 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:

  • Launch the query named “Chicago Orders Query” you designed earlier in Design view.
  • On the Design tab, in the Query Type group, pick Append. The Append dialogue box opens.
  • In the Append dialogue box, select the arrow in the Table Name box, pick Chicago Orders from the drop-down list, and then choose OK.
  • In the design grid, in the Criteria row of the Ship City column, delete ‘Chicago’, and then write ‘Milwaukee’.
  • In the Append To row, pick the appropriate field for each column. In this example, the Append To row values should match the Field row values, but that is not mandatory for append queries to work.
  • On the Design tab, in the Results group, select 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.

Top of Page

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:

  • Enter the Chicago Orders table in Design view.
  • In the Product ID row, switch the Data Type from Number to Text.
  • Save and exit the Chicago Orders table.
  • On the Create tab, in the Query group, pick Query Design.
  • In the Show Table dialogue box, double-click Chicago Orders and Products, and exit the Show Table dialogue box.
  • On the Design tab, in the Query Type group, pick Update.
  • In the design grid, the Sort and Show rows disappear, and the Update To row emerges.
  • In the Chicago Orders table, double-click Product ID to append this field to the design grid.
  • In the design grid, in the Update To row of the Product ID column, input or paste the following: [Products].[Product Name]Tip:  You can use an update query to erase field values by using an empty string (“”) or NULL in the Update To row.
  • In the Criteria row, write or paste the following: [Product ID] Like ([Products].[ID])
  • You can check which values will be updated by an update query by viewing the query in Datasheet view.
  • On the Design tab, pick View > Datasheet View. The query obtains a list of Product IDs that will be updated.
  • On the Design tab, select Run. Once you open the Chicago Orders table, you will notice 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.

Top of Page

Create a delete query

A delete query can be used to delete data from your tables, and you can use a delete query to state criteria to specify which rows should be deleted. A delete query offers 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, pick Query Design.
  • In the Show Table box, double-click Chicago Orders and exit the Show Table box.
  • On the Design tab, in the Query Type group, choose 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 create it in the grid.
  • In the design grid, in the Criteria row of the Order ID column, write Is Null.
  • On the Design tab, in the Results group, press Run.

For more information about delete queries, see Create and run a delete query.

Top of Page

Leave a Reply

%d bloggers like this: