Objective group 3. Create and modify queries – MOS Study Guide for Microsoft Access Expert Exam MO-500

Objective group 3

Create and modify queries

The skills tested in this section of the Microsoft Office Specialist Expert exam for Microsoft Access 365 and Microsoft Access 2019 relate to creating and using queries. Specifically, the following objectives are associated with this set of skills:

3.1 Create and run queries

3.2 Modify queries

If you have a large amount of data in a table, you will not usually want or need to work directly with the raw data. Instead, it is better to work with data that has been sorted and filtered in some way, two operations that you can quickly combine by creating a query. Although the name implies that queries are a sort of question, it is more useful to think of them as requests. In the simplest case, a query is a request to see a particular subset of your data in a particular order. In more complex cases, queries help you manage database records. For example, you can run a query to delete records or to append data to a table.

This chapter guides you in studying ways to create and modify queries, create calculated fields, and group within queries.

Objective 3.1: Create and run queries

You can create a query in Design view or use a wizard to create most or all of the query for you. (Access provides wizards that you use to create different kinds of queries.) In the Query Designer, you add the tables and fields a query requires and define criteria that Access applies to display or act on a specific set of records.

A query based on multiple tables in Design view.

This topic describes how to run a query and how to save and delete a query. You also examine how to create various types of queries, including select queries, crosstab queries, parameter queries, action queries, and queries that use multiple tables.

Run queries

You can run a query directly from the Navigation Pane or by using commands. Running a query displays the query results in Datasheet view, where you can then sort and filter the records, export the query’s data, and perform other operations.

Run a query to see its results in Datasheet view.

When you work in the Query Designer, as you add or remove fields and define criteria and other query properties, you can run the query to view the records that the query returns. You can then return to Design view to make additional modifications to the query and run the query again to check the effects of the changes.

To run a query from the Navigation Pane

  • Double-click the query.

  • Right-click the query, then click Open.

To run a query from Design view

  1. Open the query in Design view.

  2. On the Design tool tab, in the Results group, click Run.

Create select queries

A select query returns all or a subset of the records stored in one or more tables. When you create a select query, you specify which fields you want to use, and you can define criteria to return a specific set of records. One simple illustration of a select query is as a record source for a mailing list. For example, by using a contacts table as the basis for the query, you could include name, address, and related fields in the query without adding fields for a contact’s email address and phone number. If you’re sending a mailing to contacts in specific locations, you could define criteria that limit the records the query returns to contacts in the locations or postal codes you designate.

Exam Strategy Select queries are the basis of other types of queries described in this chapter, including crosstab queries and action queries. On Exam MO-500, Microsoft Access Expert (Access and Access 2019), you might not need to specifically demonstrate how to create a select query, but you will need the skills described in this section to create other types of queries.

Select queries (and other types of queries) also illustrate one purpose of table relationships. You can add two or more tables to a query and use their relationship to retrieve a set of records from all the tables—for example, all high-priority tasks related to projects for a particular customer, managed by a specific employee, and with a completion date within 30 days.

See Also For more information about queries that use multiple tables, see “Create multiple-table queries” later in this topic.

To create a select query, you can use the Simple Query Wizard or create the query in the Query Designer. In the Query Designer, you can add criteria to the query. The wizard provides an option that opens the Query Designer if you need it.

The Simple Query Wizard tailors its steps based on factors such as the following:

  • If you add fields from only one table and those fields store only text data (not numeric data), the wizard prompts you to name the query and specify whether to open the query to view the records it returns or open the query in Design view for modification.

  • If you include numeric or date fields or fields from more than one table, the wizard prompts you to create a detail query or a summary query. A detail query shows each individual record that the query returns. In a summary query, you can total the values in a field or determine the field’s average, minimum, or maximum value.

  • When date fields are present in a summary query, the wizard also prompts you to choose an option for how you want to group records by dates. For example, you can group records by month, quarter, or year.

When you open the Query Designer, it displays the query design grid and the Show Table dialog box. The Show Table dialog box lists all the tables and queries in the current database.

In Design view, you use Show Table to add one or more tables or queries to the query grid.

When you add more than a few tables to a query, you can reposition the field lists in the main pane of the Query Designer to view more clearly the relationship lines that link the tables. You add a field to the query by dragging it from the field list in the main pane to the Field row in a blank column in the grid at the bottom of the Query Designer, or you can select fields from the list displayed in the Field row. The asterisk at the top of the field list adds all the fields in a table to the query design grid.

By default, a query returns all matching records, but you can select a preset value (5, 25, 100, 5%, or 25%) or enter a value to specify how many records you want to display. By limiting the number of records, you can view data such as the top 20 orders that customers placed in the current month.

To create a select query by using the Simple Query Wizard

  1. On the Create tab, in the Queries group, click Query Wizard.

  2. In the New Query dialog box, with Simple Query Wizard selected in the list of wizards, click OK.

  3. In the Simple Query Wizard, use the Tables/Queries list to select the first table or query you want to use for this query.

  4. In the Available Fields list, do either of the following:

    • Select the field or fields you want to include in the query, then click the arrow (>) to move the fields to the Selected Fields list.

    • Click the chevron button (>>) to move all the fields to the Selected Fields list.

  5. Repeat steps 3 and 4 to include other tables or queries in the select query and add the fields you want to include. Then click Next.

  6. If the query includes numeric fields or fields from more than one table, the wizard prompts you to create a detail query or a summary query. A detail query is the default option. To continue creating a detail query, click Next . To create a summary query, do the following:

    Select the Summary option, then click Summary Options to display a list of the affected fields.

    Options for a summary query.

    In the Summary Options dialog box, select the check box for each summary function you want to apply to each of the fields.

    Click OK to close the Summary Options dialog box and return to the wizard.

  7. Click Next in the wizard.

  8. If the query includes a Date/Time field, click an option to specify the way you want to group dates in the query, then click Next.

    Options for grouping records by date.
  9. On the wizard’s last page, enter a name for the query, choose whether to open the query to review the results or open the query in Design view, and then click Finish.

To create a select query in Design view

  1. On the Create tab, in the Queries group, click Query Design.

  2. In the Show Table dialog box, select the tables or queries you want to use in the query. Click Add to add the objects to the Query Designer, then click Close.

  3. To add fields to the query, do one of the following:

    • From the field lists in the main pane, drag the fields you want to include in the query to the Field row in the query design grid. Access adds the field and table or query name to the grid.

      Tip You can display or hide the table row by clicking the Table Names button in the Show/Hide group on the Design tool tab.

    • Click in the Field row of the query design grid, click the arrow, and then select the field from the list.

      Tip Each field is preceded by the table or query name.

    • To add all fields in a field list to the query, double-click or drag the asterisk from the field list in the main pane to the Field row.

  4. In the Criteria row and the Or row, define selection criteria for the query depending on which records you want the query to return.

  5. To return a specific number of records, on the Design tool tab, in the Query Setup group, click the Return arrow, then select the option you want to apply or enter the value in the Return box.

  6. On the Quick Access Toolbar, click Save.

  7. In the Save As dialog box, enter a name for the query, then click OK.

Create crosstab queries

A crosstab query uses Sum, Avg, or another aggregate function to group a query’s results. In Datasheet view, a crosstab query looks something like a PivotTable in Microsoft Excel. The query’s data is grouped by two sets of values, based on fields you select. One set appears down the left side of the datasheet, and the other appears across the top. The values in the body of the query come from the field you designate as the Value field.

A crosstab query summarizing units in stock by supplier and category.

Access provides a wizard you can use to create a crosstab query, or you can use the Query Designer to specify the fields for the query and to define the calculations you want the query to perform. When you add fields to a crosstab query, you specify which fields to use as row headings (you can use as many as three fields), which field appears in the columns across the top, and which field is used for the summary values.

You can select fields from only one table or query when you use the Crosstab Query Wizard. To work around this limitation, you can create a select query that includes fields from multiple tables and then choose the select query as the record source when you work in the Crosstab Query Wizard.

In the wizard, you must identify the following information:

  • The table or query on which to base the crosstab query.

  • The field or fields (up to three fields) you want to use as row headings. If you use more than one field, Access sorts the query’s records in the order in which you select the fields. Keep in mind that using more than one field makes the query more difficult to read.

  • The field you want to use for the column headings. It’s generally good practice to choose a field that includes only a few values for the column heading field. For example, you might select a task status field with values such as Not Started, In Progress, and Complete.

  • The interval for grouping date/time information in the column headings (if you choose a Date/Time field for the column heading). You can choose Year, Quarter, Month, Date, or Date/Time.

  • The field whose value you want to summarize and the function you want to apply. Different functions are available depending on the field’s data type. This page of the wizard also includes the option Yes, Include Row Sums. Selecting this check box inserts a row heading in the query that uses the same field and function as the field value. A row sum also inserts a column that summarizes the remaining columns.

  • The name you want to assign to the query.

When you create a crosstab query in Design view, you can include multiple tables or queries as the query’s record source. You can also first create a select query that returns the records you want and use that query as the sole record source for the crosstab query.

See Also For information about creating select queries, see “Create select queries” earlier in this topic.

The design grid for a crosstab query contains a Total row and a Crosstab row (in addition to the Sort, Criteria, and Or rows you work with in select queries). You use the Crosstab row to specify which field or fields to use as row headings, which field to use for the query’s column headings, and which field to summarize for the query’s values. In the Total row, you specify the summary function that the query applies.

To create a crosstab query by using the Crosstab Query Wizard

  1. On the Create tab, in the Queries group, click Query Wizard.

  2. In the New Query dialog box, select Crosstab Query Wizard, then click OK.

  3. On the wizard’s first page, select the table or query on which to base the crosstab query, then click Next.

  4. Specify the field or fields (up to three fields) you want to use as row headings, then click Next.

  5. Select the field you want to use for the column headings, then click Next.

  6. If you chose a Date/Time field for the column heading in step 5, specify the interval for grouping date/time information in the column headings, then click Next.

  7. Select the field whose value you want to summarize and the function you want to apply, then click Next.

  8. Enter a name for the query, then click Finish.

To create a crosstab query in Design view

  1. On the Create tab, in the Queries group, click Query Design.

  2. In the Show Table dialog box, select the tables or queries you want to use in the query. Click Add to add the tables to the Query Designer, then click Close.

  3. From the table field lists, drag the fields you want to include in the query to the Field row in the query design grid. (You can also select fields from the list Access displays when you click in the Field row in the query design grid.)

  4. In the Criteria row, define any selection criteria for the query.

  5. On the Design tool tab, in the Query Type group, click Crosstab.

  6. In the Crosstab row, specify the field or fields you want to use for row headings, column headings, and the query’s values.

  7. In the Total row for the value field, select the summary function you want to apply.

  8. On the Design tool tab, in the Results group, click Run to display the query’s results.

Create parameter queries

A parameter query provides flexibility in applying criteria. Instead of adding criteria such as =“Los Angeles” to the City field, you define a parameter for that field by using a format and a prompt such as [Enter City Name]. When you run a parameter query, Access opens the Enter Parameter Value dialog box, which displays the prompt you defined. You can enter the value you want to use as criteria (for example, Minneapolis or Montreal for the city parameter). Access returns the set of records that match the criteria you provide.

When you create a parameter query, you also specify the parameter’s data type, which should match the data type for the field you defined the parameter for.

Tip You can also use parameters in crosstab, append, make-table, and update queries.

Use a parameter query to specify criteria when you run the query.

To create a parameter query

  1. On the Create tab, in the Queries group, click Query Design.

  2. In the Show Table dialog box, add the tables you want to use in the query.

  3. Add the fields you need to the query.

  4. In the Criteria row for the field you want to use as a parameter, enter the parameter prompt, enclosing the prompt in square brackets.

  5. On the Design tool tab, in the Show/Hide group, click Parameters.

  6. In the Query Parameters dialog box, in the Parameter column, enter the parameter prompt exactly as it appears in the design grid. In the Data Type column, select the data type for the parameter, then click OK.

Create action queries

Action queries are often used to help manage the records in a database. For example, you can use a select query to retrieve records for all discontinued products. You can use a make-table or an append query—two types of action queries—to archive those records. You can run an update or a delete query to update the value of a field or to remove records that match criteria you define.

Access displays a warning before you run an action query.

When you run a make-table query, Access creates a table (in the current database or in another database you designate) that’s defined by the fields included in the query. A make-table query has at least a couple of functions:

  • A make-table query can improve the performance of your database when you find yourself frequently running a select query that is based on several tables whose data doesn’t change. Access can run the select query more quickly if it is based on a single table (created by the make-table query) instead of on multiple tables.

  • You can use a make-table query to build your data archives. For example, use a make-table query to store all the orders for the past year and use the Orders table only for current orders.

A table created by a make-table query inherits field names and data types but not all settings specified for other field properties. Also, the new table does not include a primary key. Open the new table in Design view to update field properties and assign a primary key.

See Also For information about how to set a primary key, see “Objective 1.2: Manage table relationships and keys.”

Because a make-table query is based on a select query, you can run the select query first to review the records the query returns and then run the make-table query. You can run a make-table query more than once. When you do, the existing table is deleted.

An append query is similar to a make-table query, but instead of creating a table, an append query adds records to a table that is already defined. Append queries are also useful tools for archiving records. For example, you could create a table named Completed Projects and then design a query based on the Projects table and related tables to select the records you want. By running this query periodically as an append query, you create an archive of completed projects.

You can append records to a table in the current database or a different database you specify. When you create an append query, keep in mind that the data you insert by running the query must conform to the design of the destination table. After you designate a query as an append query, Access adds the Append To row to the query design grid. Based on the table you are appending records to, Access selects and displays a matching field in the Append To row. You can change the matching fields that Access provides, but the data type and other properties of the field specified in the Append To row must be compatible with the field in the query. The source data must also conform to any validation rules defined for the destination table or the fields that the table contains.

As with make-table queries, you create an append query by first defining a select query. After setting up the select query, verify that it returns the records you need by running it. If the results are correct, you can then select the table you want to append records to.

Action queries can also be used to update or delete records. For example, you can use an update query to increase the values in a price field by a specified percentage or to perform date arithmetic by adding or subtracting a specific time period to the values in a date field. A delete query removes the set of records that meets criteria you define. You can use a delete query to remove all products marked Discontinued, for example.

To create an update query or a delete query, you start by creating a select query. For an update query, Access adds the Update To row to the design grid and removes the Sort and Show rows. In the Update To row for the field or fields you want to modify, you enter the expression that will update the field’s current values. For example, to add 30 days to the ExpirationDate field, you would enter the expression [ExpirationDate]+30. When you run the update query, Access displays a message box telling you how many rows (records) will be updated.

IMPORTANT You cannot undo the changes made by an update query or a delete query. Before you run the query, you should make a backup copy of the table whose records will be updated or deleted. You can check which records will be affected before you run the query by switching the query to Datasheet view.

In a delete query, Access adds the Delete row to the query grid and removes the Show and Sort rows. In the Delete row, when you select the Where option for a field, you can specify criteria in the field’s Criteria row that select the records Access will delete. For example, you might delete all records where the Discontinued field equals Yes or all task records for which the status is marked as complete.

When you work with delete queries, you might delete records you weren’t expecting to. This occurs if the table you’re deleting records from is related to another table and the tables’ relationship is set up to use the Cascade Delete Related Records option. You can turn off this option if necessary by modifying the tables’ relationship.

See Also For more information about the Cascade Delete Related Records option, see “Objective 1.2: Manage table relationships and keys.”

To create and run a make-table query

  1. Create a select query on which to base the make-table query.

    See Also For more information about how to create a select query, see “Create select queries” earlier in this topic.

  2. With the select query open in Design view, on the Design tool tab, in the Results group, click Run.

  3. Review the records returned by the select query in Datasheet view.

  4. On the Home tab, in the Views group, click View, then click Design View to return the query to Design view.

  5. On the Design tool tab, in the Query Type group, click Make Table.

  6. In the Make Table dialog box, enter a name for the table, then do one of the following:

    • To have Access create the table in the current database, click Current Database.

    • To have Access create the table in another database, click Another Database. Then either enter the file name in the File Name box or click Browse, navigate to and select the file, and then click OK to return to the Make Table dialog box.

  7. Click OK to close the Make Table dialog box.

  8. On the Design tool tab, in the Results group, click Run.

  9. In the Microsoft Access message box alerting you that you’ll be pasting rows into a new table and the operation can’t be undone, click Yes.

To create and run an append query

  1. Create a select query on which to base the append query.

    See Also For more information about how to create a select query, see “Create select queries” earlier in this topic.

  2. With the select query open in Design view, on the Design tooltab, in the Results group, click Run.

  3. Review the records returned by the select query in Datasheet view.

  4. On the Home tab, in the Views group, click View, then click Design View to return the query to Design view.

  5. On the Design tooltab, in the Query Type group, click Append to open the Append dialog box.

  6. If you want to add the records to a table in a different database, click Another Database, and then either enter the database name in the File Name box or click Browse, navigate to and select the file, and then click OK.

  7. In the Append dialog box, expand the Table Name list and click the table you want to add the records to. Then click OK.

  8. On the Design tool tab, in the Results group, click Run.

  9. In the Microsoft Access dialog box asking you to confirm the operation, click Yes.

To create and run an update query

  1. Create a select query on which to base the update query.

    See Also For more information about how to create a select query, see “Create select queries” earlier in this topic.

  2. On the Design tool tab, in the Query Type group, click Update.

  3. In the Update To row for the field or fields you want to update, enter an expression that calculates the updated values.

  4. On the Design tool tab, in the Results group, click Run.

  5. In the Microsoft Access dialog box asking you to confirm the operation, click Yes.

To create and run a delete query

  1. Create a select query on which to base the delete query.

    See Also For more information about how to create a select query, see “Create select queries” earlier in this topic.

  2. On the Design tool tab, in the Query Type group, click Delete.

  3. In the Delete row, select Where for each field on which you want to apply criteria for the deletion.

  4. In the Criteria row, specify the criteria for selecting the records you want to delete.

  5. On the Design tool tab, in the Results group, click Run.

  6. In the Microsoft Access dialog box asking you to confirm the operation, click Yes.

Create multiple-table queries

You can create a multiple-table query to return a set of records from related tables or from tables you join for the query itself. For example, you can join the Customers table to the Orders table by using the CustomerID field.

Whenever you have relationships defined between two tables, Access automatically joins the tables by using the fields in the defined relationships. Access also includes an option named Enable AutoJoin. This option is enabled by default, so when you create a query that includes tables that aren’t directly related, Access tries to link the tables for you by examining the primary key fields for each table and then looking for a field with the same name and data type in one of the other tables in the query. If Access doesn’t find a match, you can link the tables yourself. By joining the tables in this way, you link them for the purposes of designing and running the query. You don’t create a permanent table relationship.

Tip You can toggle the Enable AutoJoin feature on and off from the Object Designers page of the Access Options dialog box.

See Also For more information about table relationships, see “Objective 1.2: Manage table relationships and keys.”

To retrieve the records you need in a query, you use either an inner join or an outer join. The default join is an inner join. With an inner join, a query returns only records with matching rows in both tables. For example, a query that joins a projects table and a tasks table returns records only for projects that have assigned tasks and for tasks that are assigned to specific projects. By using an outer join in this query, you can retrieve the set of matching records (projects and their assigned tasks) in addition to projects without tasks (all projects) or tasks without projects (all tasks).

You can create a “left” outer join or a “right” outer join to retrieve all the records from one of the tables. Left and right refer to how the tables are identified in the Join Properties dialog box. Access provides options to create an outer join that returns all records from one table and matching records from another, depending on which table’s records you want to view.

See Also For more information about joins, see “Set relationships” in “Objective 1.2: Manage table relationships and keys.”

Outer joins return all records from one table and matching records from another.

Tip You can also specify the join type that related tables use when you have the Relationships window open. Click the relationship line for the tables you want to work with, then click Edit Relationships in the Tools group on the Relationship Tools Design tool tab. Click Join Type in the Edit Relationships dialog box, then click the option for the type of join you want to use.

To add tables to a query

  • In the Query Designer, right-click an empty area of the main pane (not on a field list), then click Show Table.

Or

  1. Open the query in Design view.

  2. On the Design tool tab, in the Query Setup group, click Show Table.

  3. In the Show Table dialog box, select the tables or queries you want to add, click Add, and then click Close.

To remove a table from a query

  1. Open the query in Design view.

  2. Right-click the field list for the table, then click Remove Table.

To set up an outer join for tables in a query

  1. In the Query Designer, right-click the line that links the tables, then click Join Properties.

  2. In the Join Properties dialog box, click one of the following options, then click OK :

    • To include only rows where the joined fields from both tables are equal, click option 1.

    • To include all records from the left table and only matching records from the right table, click option 2.

    • To include all records from the right table and only matching records from the left table, click option 3.

Save queries

Access automatically saves a query you create by using one of the query wizards. The wizard provides a default name for the query based on the first (or only) table or query you select as the query’s data source. When you design a query in the Query Designer, Access assigns a default name such as Query1. You can replace either default name with a more meaningful one. You cannot use the same name for a table and a query. To avoid this conflict, you can include a prefix such as qry in each query’s name.

You can use options on the Save As page in the Backstage view to create a copy of a query as a new database object (a new query, form, or report) or as a PDF or an XPS file. Saving a query as a new database object can be helpful if you want to experiment with the query by adding additional selection criteria, for example, but don’t want to risk inadvertent changes to the original query. A form or report you create by saving a query as a database object contains the query’s fields and provides a starting point from which you can further develop the object you create. When you save a query as a PDF or an XPS file, you create a static copy of the query’s data. You can specify a range of pages to save and also set accessibility options.

When you delete a query from a database, keep in mind that queries are often used as the record source for forms and reports. If you delete a query that is the basis of a form or report, you must update the record source before you can use the form or report to view records.

To save a query from Design view

  1. On the Quick Access Toolbar, click Save.

  2. In the Save As dialog box, enter a name for the query, then click OK.

To save a query as a database object

  1. In the Navigation Pane, right-click the query, then click Open.

  2. Click the File tab, then click Save As.

  3. In the File Types list, click Save Object As.

  4. In the Save The Current Database Object area, under Database File Types, click Save Object As, then click Save As.

  5. In the Save As dialog box, in the Save QueryName to box, enter a name for the query.

    Saving a query as a new database object.
  6. In the As box, select Query, Form, or Report.

  7. Click OK.

To save a query as a PDF or an XPS file

  1. In the Navigation Pane, right-click the query, then click Open.

  2. Click the File tab, then click Save As.

  3. In the File Types list, click Save Object As.

  4. In the Save The Current Database Object area, under Database File Types, click PDF Or XPS, then click Save As.

  5. In the Publish As PDF Or XPS dialog box, in the File Name box, modify the name Access supplies if necessary.

  6. In the Save As Type list, select PDF or XPS Document.

  7. To set a page range or other options, click Options, specify the options you want to use, and then click OK in the Options dialog box.

  8. In the Publish As PDF Or XPS dialog box, click Publish.

The practice file for these tasks is located in the MOSAccessExpert2019\Objective3 practice file folder. The folder also contains a result file that you can use to check your work.

  • In the AccessExpert_3-1 database, do the following:

    • Use the Simple Query Wizard to create a query from the Categories table that includes only the CategoryName and Description fields. Name the query Simplified Categories. Choose the option to view information so that you can examine the query’s results.

    • Close the Simplified Categories query.

    • Use the Query Designer to create a select query based on the Products table. Add the fields ProductName, CategoryID, and UnitPrice, and sort the CategoryID field in ascending order. Save the query as Products by Category.

    • Use the Save As command to save Products by Category to a new query named Update Product Prices. Convert this query to an update query that increases unit prices by 10 percent. Save and close the query.

    • Use the Query Designer to create a select query based on the Categories, Products, and Suppliers tables. Add the fields CategoryName from the Categories table, CompanyName from the Suppliers table, and UnitsInStock from the Products table. Save the query as Units in Stock by Supplier and Category. Run the query to display the records returned by the select query. Now convert this query to a crosstab query. Use the CompanyName field as the row heading field and the CategoryName field as the column heading field. Use the UnitsInStock field as the value field. Select Sum in the Total row for the UnitsInStock field. Save and close the query.

    • Use the Query Designer to create a select query based on the Tasks and Projects tables. From the Tasks table, add the TaskName, TaskDescription, and TaskCompleted fields; from the Project table, add the ProjectName field. Run the query to display the results.

    • Use the options in the Join Properties dialog box to create an outer join that shows all the records from the Projects table. Run the query again to view how the outer join changes the query’s results.

  • Open the AccessExpert_3-1_results database. Compare the two databases to check your work. Then close the open databases.

Objective 3.2: Modify queries

After you set up a query, you can modify it by renaming it, changing or rearranging the fields, showing and hiding query fields, and sorting the query’s results. You can also format the fields in a query. This topic examines some of the ways you can modify a query.

Change the fields in a query

With a query open in Design view, you can add, remove, and rearrange the query’s fields in several ways. To add fields, you can use the field list or the Field row in the design grid. When you drag a field between two fields already in the grid, Access moves the other fields in the query to the right. You can also use the options in the Show Table dialog box to add another table or query to the query and then include fields from those objects in the query’s design. When you remove a field, you cannot use the Undo command to reverse this action.

You can also reposition the fields in a query design grid.

To add fields to a query

  1. Open the query in Design view.

  2. Do either of the following:

    • In the field list in the top pane of the Query Designer, select the field, then drag the field to the query design grid.

    • In the Field row in a blank column in the design grid, select the field you want to add.

To delete a field

  • In the design grid, click the field column header, then press Delete.

To insert a column in the design grid

  1. In the query design grid, click in the column that you want to insert a new column to the left of.

  2. On the Design tool tab, in the Query Setup group, click Insert Column.

To delete a column in the design grid

  1. In the query design grid, click in the column that you want to delete.

  2. On the Design tool tab, in the Query Setup group, click Delete Column.

To change the order of the fields in the design grid

  • Click the top of the column for the field you want to move (hold down the Shift key to select more than one column), then drag the column or columns to the new location.

Show and hide query fields

By default, the check box in the Show row for each field in a query is selected. This means that the values in each field are displayed in the query’s results. By clearing this check box for a field, you remove that field’s values from the query’s result without removing the field from the query.

Access shows only those fields selected in the Show row.

The capability to hide a field is helpful when you want to use a field to define selection criteria or to sort a query but don’t want to show the field in the query’s results. Fields you use this way are essential to defining the query, but their values don’t need to be shown in the query’s results. You might add an ID field or a date field to a query for these purposes. For the ID field, you might specify a customer’s ID. You could use the date field to sort records to filter orders to show only a time-based subset (such as your previous fiscal year). The purpose of these fields is to tailor the query—any reporting or analysis doesn’t require that the query include the values that these fields provide.

To show and hide query fields

  1. Open the query in Design view.

  2. In the query design grid, clear the Show check box for any fields you want to hide.

  3. Select the Show check box to display a field in the results.

Sort data within queries

You use the Sort row in the query design grid to specify how Access sorts the records returned by a query. You can sort by a single field or by more than one field. When you specify a sort order for more than one field, Access sorts records according to the order in which the fields appear left to right in the query design grid.

Tip If you add all the fields from a table or query to the query design grid by dragging the asterisk, you cannot use the Sort row to sort records.

If you want to sort by multiple fields in a specific sequence but also display one of these fields later in the order of the fields, you can add a second instance of the field, set the sort order for the field, and then hide the second instance of the field so that it doesn’t appear in the query’s results.

The sorting options are Ascending, Descending, and Not Sorted. Be sure to reposition fields as you want them to appear when you are sorting records by more than one field.

See Also For more information about how to rearrange field order, see “Change the fields in a query” earlier in this topic.

To use the Sort row in a query

  1. Open the query in Design view.

  2. In the query design grid, click in the Sort row for the field you want to sort by, click the arrow, and then select Ascending, Descending, or Not Sorted.

  3. To sort by more than one field, specify the sort order for the additional fields. In the query design grid, arrange the fields from left to right in the order you want Access to use them to sort records.

Use operators

Access defines a set of operators you can use in expressions that define query criteria, filters, or calculated fields. The operators you can use include basic arithmetic operators for addition (+), subtraction (–), multiplication (/), and division (*). You can use the ampersand (&) to combine the values in two or more text fields. For example, the expression [City] & “, “ & [State/Province] combines the City and State/Province fields in a single text string.

Access also provides logical operators, such as Or, And, and Not, and comparison operators, such as < (less than) and > (greater than). Here are a few examples of how to use these operators in criteria expressions.

Expression

Result

<Date()

Returns records with a date earlier than the current date

"Lee" or "Andersen"

Returns records with either Lee or Andersen as the value in the field

Not "Andersen"

Returns records except those with Andersen as the value in the field

Not <#4/1/2020#

Returns only records with a date later than 4/1/2020

<=50

Returns records with a value of 50 or less

<>"Beverages"

Returns records that do not equal Beverages in this field

Three other comparison operators are Like, In, and Between. The Like operator can be used to compare a field value to a text string. For example, the expression Like “98###” in a postal code field returns records with ZIP Code values that start with 98. You can use the In operator to find specific records. The expression In (“Las Vegas”) returns records with the value Las Vegas in the city field. Use the Between operator to select records within a range of dates (Between #1/1/2020# And #3/31/2020#) or a range of numbers (Between 1200 And 1500).

Filter data within queries

A select query often includes criteria that defines the subset of records the query returns when you run it. For example, to find records for customers in a specific city, you can add the City field to the query and then enter an expression such as =“CityName” (where “CityName” is the city you want to examine) to the Criteria row. You must enclose text values in quotation marks.

To specify criteria for a date field, enclose the date (or dates) in pound signs (#). You can retrieve records for orders placed between two dates by using an expression such as Between #4/1/2017# And #6/30/2017#. You can also use comparison operators to retrieve records that are less than (<) or greater than (>) a certain numeric amount.

See Also For more information about the operators you can use in a query, see “Use operators” earlier in this topic.

When you enter criteria in the Criteria row for more than one field, the query selects only records that match the criteria in all those fields—for example, records that have a value in the Order Date field greater than 9/15/2020 and a value of Fabrikam for the Company Name field. You can set up OR criteria (to find records with a value of Fabrikam or Contoso in the Company Name field, for example) by entering the second criterion in the Or row (below the Criteria row).

A query filtered with Or criteria.

You can filter the results of a query in Datasheet view by applying the filtering tools and options available for filtering records in a table.

See Also For more information about filtering results, see “Filter records” in “Objective 2.3: Manage table records.”

To set filter criteria

  1. In the query design grid, click in the Criteria row for the field you want to filter by.

  2. Enter the expression for the criteria you want to apply to the field.

  3. To define criteria for more than one field, do either of the following:

    • To apply And criteria, click in the Criteria row for another field, then enter the expression to use as a filter.

    • To apply Or criteria, click in the Or row for another field, then enter the expression to use as a filter.

  4. Run the query to display the results.

Format fields within queries

At times, you might want to print the results of a query or save the results as a PDF file for distribution. To enhance the plain display of the query’s results in the datasheet, you can apply text formatting. For example, you can display or hide gridlines, apply a different background color to alternate rows, select a different font and font size, and apply font attributes such as bold or italic.

The text formatting you apply affects all the records in a query. You can’t, for example, apply bold formatting to only one column of values in the query’s datasheet. Adding or modifying alternate row colors and displaying gridlines help distinguish the rows and columns of data.

In a query, you can use a field’s Format property to display the values in that field differently from the way the field’s format is specified in the table in which the field is defined. For example, a date field can be defined with the Short Date format in its table but displayed in the Long Date format in a query. You can also use a field’s Caption property in a query to display a different label in the column heading. Setting the Format or Caption property for a field in a query does not define or change the property for the field in its table.

To apply text formatting to a query

  1. Open the query in Datasheet view.

  2. On the Home tab, in the Text Formatting group, do any of the following:

    • In the Font, Font Size, or Font Color list, select a different font, font size, or font color.

    • Click the Bold, Italic, or Underline button to format the text.

    • Click the Background Color arrow, then select a background color for odd-numbered rows.

    • Click the Gridlines arrow, then select to show both horizontal and vertical gridlines, only horizontal gridlines, only vertical gridlines, or no gridlines.

    • Click the Alternate Row Color arrow and select a color that is applied to even-numbered rows.

To set properties for a field in a query

  1. Open the query in Design view.

  2. On the Design tool tab, in the Show/Hide group, click Property Sheet.

  3. Click in the column for the field you want to format.

  4. In the property sheet, enter or select a value for properties such as Format and Caption.

The practice file for these tasks is located in the MOSAccessExpert2019\Objective3 practice file folder. The folder also contains a result file that you can use to check your work.

  • Open the AccessExpert_3-2 database.

  • Open the Customer Orders By Category query in Design view, and make the following changes. Run the query after each change to check how the change affects the query’s results.

    • From the Products table, add the ProductName and UnitsInStock fields to the query.

    • Hide the OrderID field.

    • Sort the query in ascending order on the OrderDate field.

    • Using operators, add criteria that filter the orders to those placed in 2019 in the Beverages and Dairy Products categories.

    • Open the query in Datasheet view.

    • Apply the Blue, Accent 1 theme color as the alternating row color.

    • Save and close the query.

  • Open the AccessExpert_3-2_results database. Compare the two databases to check your work. Then close the open databases.