6 Common Microsoft Access Queries

For your pleasure and entertainment, here are six useful types of queries you can perform in Microsoft Access. Access 2019 offers a handful of other queries, but these are common queries. Those queries are pretty complicated. If you become adept at querying, however, you’re invited to look into the Help system in Access for advice about running the query types that aren’t explained here.

Microsoft Access 2019: Select query

A select query is the standard kind of query. A select query gathers information from one or more database tables and displays the information in a datasheet. A select query is the most common query, the primal query, the starting point for most other queries.

Microsoft Access 2019: Top-value query

A top-value query is an easy way to find out, in a Number or Currency field, the highest or lowest values. On the Query grid, enter the name of the Number or Currency field you want to know more about; then choose Ascending in the Sort drop-down list to rank values from lowest to highest or Descending in the Sort drop-down list to rank values from highest to lowest. Finally, on the (Query Tools) Design tab, enter a value in the Return text box or choose a value on the Return drop-down list:

• Highest or lowest by percentage: Enter or choose a percentage to find, for example, the highest or lowest 25 percent of the values. To enter a percentage, type a percent sign (%) after your entry and press the Enter key.
• Highest or lowest by ranking number: Enter or choose a number to find, for example, the top-ten or lowest-ten values. Press the Enter key after you enter a number.

This may seem counterintuitive, but to see the top values, you have to sort the field you’re ranking in descending order. For example, if you sort employees by number of sales in descending order, the employees with the top sales appear at the top. To see the bottom values, sort in ascending order.

Microsoft Access 2019: Summary query

Similar to a top-value query, a summary query is a way of getting cumulative information about all the data in a field. In a field that stores data about sales in Kentucky, for example, you can find the average amount of each sale, the total amount of all the sales, the total number of all the sales, and other data.

To run a summary query, go to the (Query Tools) Design tab and click the Totals button. A new row called Total appears on the Query grid. Open the Total drop-down list in the field whose contents you want to summarize and choose a function.

The Group By, Expression, and Where choices in the Totals drop-down list are for including fields you're not performing a function on:

• Group By: For choosing which fields to show totals for.
• Expression: For creating a calculated field.
• Where: For setting criteria (you can’t include the field in the query).

Microsoft Access 2019: Calculation query

A calculation query is one in which calculations are performed as part of the query. For example, you can calculate the sales tax on items sold or total the numbers in two fields in the same record. The beauty of a calculation query is that the data is recomputed each time you run the query. If the data used to make a calculation changes, so does the result of the calculation. If you were to include the calculation in a database table, you would have to recalculate the data yourself each time one of the values changed. With a calculation query, Access does the math for you.

To construct a calculation query, you create a new field in the Query grid for storing the results of the calculation; then enter a name for the field and a formula for the calculation. Follow these steps to create a calculation query:

1. Create a query as you normally would and be sure to include the fields you want to use for calculation purposes in the Query grid.
2. In the Field box of a blank field, enter a name for the Calculation field and follow it with a colon.
   In the image below, Subtotal: was entered. The purpose of the new Subtotal field is to multiply the Unit Price by the Quantity.
   : A calculation query.
3. After the colon, in square brackets ([>), enter the name of a field whose data you use for the calculation.

   Data from the Unit Price and Quantity fields are used in the calculation, so their names appear in square brackets: [Unit Price> and [Quantity>. Be sure to spell field names correctly so that Access can recognize them.

4. Complete the calculation.
   How you do this depends on what kind of calculation you’re making. In the image above, an asterisk (*) was entered to multiply one field by another. The equation multiplies the values in the Unit Price and Quantity fields. You can add the data from two different fields — including calculated fields — by putting their names in brackets and joining them with a plus sign, like so: [SubTotal>+[Shipping Cost>.

Sometimes the results of the query aren’t formatted correctly on the datasheet. To assign a new format to a field that you create for the purposes of making a calculation query, right-click the field on the Query grid and choose Properties. The Property Sheet appears. On the General tab, click the Format drop-down list and choose the correct format for your new, hand-crafted field.

Microsoft Access 2019: Delete query

Be careful about running delete queries. A delete query deletes records and doesn’t give you the opportunity to get the records back if you change your mind about deleting them. If used skillfully, however, a delete query is a great way to purge records from more than one database table at one time. Back up your database file before running a delete query.

To run a delete query, start a new query, and on the (Query Tools) Design tab, click the Delete button. Then make as though you were running a select query but target the records you want to delete. Finally, click the Run button to run the query.

You can delete records from more than one table as long as the tables are related and you chose the Cascade Delete Related Records option in the Edit Relationships dialog box when you linked the tables.

To preview the records that will be deleted before you run the delete query, switch to Datasheet view (click the View button). Those records you see? The delete query will delete them if you click the Run button.

Microsoft Access 2019: Update query

An update query is a way to reach into a database and update records in several different tables all at one time. Update queries can be invaluable, but as with delete queries, they can have untoward consequences. Back up your database before you run an update query; then follow these steps to run it:

1. Starting in Design view, go to the (Query Tools) Design tab and click the Update button.
2. In the field with the data that needs updating, enter text or a value in the Update To line. You can even enter another field name in square brackets ([>).

   What you enter in the Update To line replaces what’s in the field of the records you collect.

3. Click the Run button.
   To update records in more than one table, you must have chosen the Cascade Update Related Fields option in the Edit Relationships dialog box when you linked the tables.