MS Access —

Review: To Create a Query in Design View

To Create a Query in Design View: Click the Queries icon in the Objects bar, then double-click Create query in Design view. Select the table or query you want to use and click Add. Repeat as necessary for additional tables or queries that you want to add to the query and click Close when you're finished.

Double-click the fields that you want to appear in the query or click and drag the fields onto the design grid. In the design grid, enter any desired search criteria for the field in the Criteria row and/or click the Sort box list arrow for the field and a sort order. Click the Save button on the toolbar, click Yes to save the query, enter a query name, and click OK.

Understanding the Different Types of Queries

The Types of Queries Are:

  • Select queries

  • Parameter queries

  • Crosstab queries

  • Make-table queries

  • Append queries

  • Delete queries

  • Update queries

  • Union queries

Creating a Multiple-Table Query

To Create a Multiple-Table Query in Design View: Click the Queries icon in the Objects bar, then double-click Create query in Design view. Select the table or query you want to use and click Add. Repeat as necessary for additional tables or queries that you want to add to the query and click Close when you're finished. If Access doesn't automatically join the tables, click the related field in the first table and drag it to the related field in the second table. Repeat as necessary to connect all the tables. Double-click the fields that you want to appear in the query or click and drag the fields onto the design grid. In the design grid, enter any desired search criteria for the field in the Criteria row and/or click the Sort box list arrow for the field and select a sort order. Click the Save button on the toolbar, click Yes to save the query, enter a query name, and click OK.

Creating a Calculated Field

To Create an Expression or Calculation in a Query: Display the query in Design view. Click the Field row of a blank column in the design grid, enter the field name for the calculated field followed by a : (colon), then enter the expression you want Access to calculate, using the proper syntax (or you can use the Expression Builder to help you create this expression).

Working with Expressions and the Expression Builder

To Create an Expression with the Expression Builder: Display the query in Design view and click the Field row of a blank column in the design grid. Click the Build button on the toolbar, double-click the field you want to use in the calculation, click the button that corresponds to the calculation you want, and then click or type any other fields or values you want to use. Click OK when you're finished.

Using an IIF Function

To Create an IIF (IF...THEN) Function: Display the query in Design view. Click the Field row of a blank column in the design grid, enter the field name followed by a : (colon) and type the expression using the syntax IIf(expr, truepart, falsepart) (or you can use the Expression Builder to help you create the IIf expression).

Summarizing Groups of Records

To Calculate or Summarize a Group of Records: Display the query in Design view and, if necessary, click the Totals button on the toolbar. Move the field that you want to group data by onto the design grid and make sure Group By appears in that field's Total row. Move the field that you want to perform calculations on onto the design grid. Choose the type of calculation that you want to perform by clicking the field's Total row, clicking the list arrow, and selecting a calculation from the list.

Display Top or Bottom Values

To View Top or Bottom Values: Display the query in Design view, click the appropriate Sort field, and select either Ascending (displays bottom values) or Descending (displays top values). Select an option from the Top Values list on the toolbar.

Parameter Queries

To Create a Parameter Query: Display the query in Design view, click the Criteria row for the field you want to use for your parameter criteria and enter the text of the prompt, surrounded by square brackets [ ].

Finding Duplicate Records

To Use the Find Duplicates Query Wizard: In the Database window, click the Queries icon in the Objects bar and click the New button. Select Find Duplicates Query Wizard and click OK, then select the table or query you want to search and click Next. Double-click the field(s) that may contain the duplicate values and click Next. Double-click any additional fields that you want to appear in the query results, click Next, and then click Finish.

Finding Unmatched Records

To Use the Find Unmatched Query Wizard: From the Database window, click the Queries icon in the Objects bar and click the New button. Select Find Unmatched Query Wizard and click OK, select the table whose values you want to display and click Next. Select the table that contains the related records, click Next, specify the related fields that join the two tables, then click the button to join the two tables and click Next. Double-click any additional fields that you want to appear in the query results, click Next, and then click Finish.

Crosstab Queries

To Create a Crosstab Query: In the Database window, click the Queries icon in the Objects bar and click the New button, then select Crosstab Query Wizard and click OK. Select the table or query you want to use in the crosstab query and click Next. Select the field you want to use as the row heading, click Next, select the field you want to use as the column heading, and click Next. Select the field you want to summarize, the type of calculation you want to use to summarize the field, click Next, type a name for the crosstab query, and click Finish.

Delete Queries

To Create a Delete Query: In the Database window, click the Queries icon in the Objects bar and click the New button. Select Design view and click OK. Add the appropriate tables and/or queries and click Close, then connect any unrelated tables. Click the Query Type button list arrow on the toolbar and select Delete Query or select Query » Delete Query from the menu. Click the View button to view the results of the delete query. If you're satisfied that the appropriate records will be deleted, click the Run button on the toolbar and click Yes to confirm the deletion.

Append Queries

To Create an Append Query: Create a new query, select Design view, and click OK. Click the tables and/or queries you want to use in the append query, click Add, and then click Close when you're finished. Click the Query Type button list arrow on the toolbar and select Append Query or select Query » Append Query from the menu. Select the table to which you want to add the results of the query. If you select an existing table, click one of the following options: Current Database (if the table is in the currently open database) or Another Database (and type the name of the other database, including the path, if necessary). Click OK, then add the fields you want to append and identify a matching field if Access doesn't supply one. Click OK and click the View button on the toolbar to view the results of the query or the Run button on the toolbar to append the records.

Make-Table Queries

To Create a Make-Table Query: In Design view, create a select query; including any tables, fields, calculated fields, and criteria. Click the Query Type button list arrow on the toolbar and select Make-Table Query or select Query » Make-Table Query from the menu. Type the name of the table you want to create, or click the drop-down list and select a table from the list if you want to replace the existing one. If you select an existing table, click one of the following options: Current Database (if the table is in the currently open database) or Another Database (and type the name of the other database, including the path, if necessary). Click OK. Click the View button on the toolbar to view the results of the query or the Run button on the toolbar to create the new table.

Update Queries

To Create an Update Query: Create a new query in Design view, then select the tables and/or queries you want to use in the update query. Click the Query Type button list arrow on the toolbar and select Update Query or select Query » Update Query from the menu. Double-click the fields that you want to appear in the query or click and drag the fields onto the design grid. Enter an expression to update the selected field and enter any criteria, if needed, to select which records should be updated. Click the View button to view the results of the update query. If you're satisfied that the appropriate records will be updated, click the Run button on the toolbar to update the records.

Quiz

  1. Which of the following criterion is NOT written using the proper syntax?

    1. "Harris"

    2. Between 1/1/2000 and 12/31/2000

    3. NO VALUE

    4. 500

  2. Which of the following types of queries are action queries? (Select all that apply.)

    1. Parameter queries.

    2. Append queries.

    3. Update queries.

    4. Crosstab queries.

  3. Which of the following expressions is NOT written in the correct syntax?

    1. [Order Total]*[Tax Rate]

    2. "Order Total"*0.1

    3. [tblCustomerTours]![Cost]*[tblEmployees]![Commission]

    4. 100+10

  4. If you are having trouble remembering how to write expressions using the correct syntax, you can use the Expression Builder to help you create the expression. (True or False?)

  5. Rebate: IIF([Age]65,"Senior","Adult") This expression is an example of:

    1. Something I learned back in high school algebra and thought I would never see again.

    2. A financial expression.

    3. Something that belongs in a Microsoft Excel book.

    4. A conditional expression.

  6. A query prompts a user for a date and then displays only records that contain the specified date. Which type of query is this?

    1. A parameter query.

    2. A crosstab query.

    3. An action query.

    4. An update query.

  7. You must create a report if you want to calculate totals for a group of records, as queries can't perform this task. (True or False?)

  8. A query summarizes information in a grid, organized by regions and months. Which type of query is this?

    1. A parameter query.

    2. A crosstab query.

    3. An action query.

    4. An update query.

  9. Your company finally agreed to buy you a nifty 3COM Palm palmtop. Now you want to extract your clients from the company's database and put them into a separate table that you can export to your Palm. Which type of query could help you accomplish this task?

    1. A parameter query.

    2. A crosstab query.

    3. An update query.

    4. A make-table query.

  10. If you are creating a crosstab query, what must the table you are querying contain?

    1. At least one text field.

    2. At least one number field.

    3. More than 100 records.

    4. Lots of confusing information.

  11. How can you add a table to the query design window?

    1. Select Edit » Add Table from the menu.

    2. Click the Show Table button on the toolbar.

    3. Select the table from the Table list on the toolbar.

    4. Select Tools » Add Table from the menu.

  12. You want a query to calculate the total sales for your employees. How can you do this from the query design window?

    1. Click the Totals button on the toolbar. In the Total row select "Group By" under the Employee field and "Sum" under the Sales field.

    2. Click in the Sales field and click the AutoSum button on the toolbar.

    3. You need to export this information to Microsoft Excel and calculate it there.

    4. Click in the Sales field and select Tools » AutoSum from the menu.

Practical work

  1. Open the Homework database.

  2. Using the Customers and Insurance Claims tables, create a multi-table query that counts the total number of claims, grouped by the State field.Hint: Use the Count function on any field in the Insurance Claims table.

  3. Sort the results of the query alphabetically by date.

  4. Add criteria to the query that excludes records from the state of Wisconsin (WI).

  5. Change the select query to a delete query and delete all records from the state of Wisconsin (WI).

  6. Change the delete query back into a select query. Remove the current criteria and add parameter criteria that ask for the beginning and ending date.

  7. Create a concatenated field that combines the FirstName and LastName fields.

  8. Save the query as "Homework Query."

  9. Close the query and the Homework database.