Creating and working with queries in MS Access

Objective: Creating and working with queries in MS Access

Introduction

   A query is a request for data results, for action on data, or for both. You can use a query to answer a simple question, to perform calculations, to combine data from different tables, or even to add, change, or delete table data. Queries that you use to retrieve data from a table or to make calculations are called select queries. Queries that add, change, or delete data are called action queries.

  You can also use a query to supply data for a form or report. In a well-designed database, the data that you want to present by using a form or report is often located in several different tables. By using a query, you can assemble the data that you want to use before you design your form or report.

Creating a Query

1. Open the database that you previously set up by using these steps. If not, then create a database. We are having a previously created database here. 
2. On the Create tab, in the Queries group, click Query Design. 
3. In the Show Table dialog box, on the Tables tab, double-click on object to insert. 
4. Close the Show Table dialog box. 
5. In the object table, double-click on the fields you want to show in the query.
6. On the Design tab, in the Results group, click Run. 
7. The query runs, and then displays the query result.

1. Ask variations of a question by using parameters with a query

1. In the Navigation Pane, right-click the query name (that you created in the previous section), and then click Design View on the shortcut menu.
2. In the query design grid, in the Criteria row of the City column, then type [For what age?].
3. The string [For what age?] is your parameter prompt. [NOTE Neither a period (.) nor an exclamation point (!) can be used as text in a parameter prompt.]
4. Select the check box in the Show row of the column, so that the query results will display that field.
5. On the Design tab, in the Results group, click Run. The query prompts you to enter a value for prompt.
6. Type value for the prompt, and then press ENTER. 
7. The query runs, and then displays the result.

To make parameter more flexible, use wildcard characters as part of the prompt:

1. Open query in Design View. 
2. In the query design grid, in the Criteria row of the Address column, type Like [For what city?]&"*". 
3. On the Design tab, in the Results group, click Run. 
4. At the query prompt, enter value, and then press ENTER.
5. The query runs, and then displays result.

SPECIFY PARAMETER DATA TYPES

   You can also specify what type of data a parameter should accept.

[NOTE 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:

1. With the query opened in Design view, on the Design tab, in the Show/Hide group, click Parameters. 
2. 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. 
3. In the Data Type column, select the data type for each parameter.0.

2. Add data to a table by using data from other tables

   You can use an append query to retrieve data from one or more tables and add that data to another table.

1. Open the query in Design view. 
2. On the Design tab, in the Query Type group, click Append. The Append dialog box opens. 
3. In the Append dialog box, click the arrow in the Table Name box, and then select table from the drop-down list. 
4. Click OK. 
5. The Append dialog box closes. In the design grid, the Show row disappears, and the Append To row appears.
6. In the design grid, in the Criteria row of the column, type some value for the column field. 
7. In the Append To row, select the appropriate field for each column. [NOTE The Append To row values should match the Field row values].
8. On the Design tab, in the Results group, click Run.

3. Change data in an automated fashion

   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.

You can update the values in the table by using the following procedure:

1. Open the table in Design view. 
2. In the ID row, change the Data Type from Number to Text. 
3. Save and close the table. 
4. On the Create tab, in the Queries group, click Query Design. 
5. In the Show Table dialog box, double-click Chicago Orders and Products. 
6. Close the Show Table dialog box. 
7. On the Design tab, in the Query Type group, click Update. 
8. In the design grid, the Sort and Show rows disappear, and the Update To row appears.
9. In the Chicago Orders table, double-click Product ID to add this field to the design grid. 
10. In the design grid, in the Update To row of the Product ID column, type or paste the following: 
11. [Products].[Product Name]
12. TIP You can use an update query to delete field values by using an empty string ("") or NULL in the Update To row.
13. In the Criteria row, type or paste the following: 
14. [Product ID] Like ([Products].[ID])
15. The Like keyword is necessary because the fields that you are comparing contain different data types (Product ID is text data, ID is numeric data).
16. You can review which values will be changed by an update query by viewing the query in Datasheet view. 
17. On the Design tab, in the Results group, click View, and then click Datasheet View.
18. The query returns a list of Product IDs that will be updated.
19. On the Design tab, in the Results group, 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.

4. Delete data in an automated fashion

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

In the Show Table dialog box, double-click table. 

Close the Show Table dialog box. 

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 table, double-click ID to add it to the grid. 

In the design grid, in the Criteria row of that column, type [enter id to delete record]. 

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

5. Create and run an update query

STEP 1: CREATE A SELECT QUERY TO IDENTIFY THE RECORDS TO UPDATE

1. Open the database that contains the records you want to update. 

2. On the Design tab, in the Macros & Code group, click Query Design. The query designer opens, and the Show Table dialog box opens.

3. Click the Tables tab. 

4. Select the table or tables that contain the records that you want to update, click Add, and then click Close. 

The table or tables appear as one or more windows in the query designer, and the windows list all the fields in each table. This figure shows the query designer with a typical table.

Table shown in the query designer

Query design grid

5. Double-click the fields that you want to update in the table windows. The selected fields appear in the Field row in the query design grid. 

You can add one table field per column in the query design grid.

To add all the fields in a table quickly, double-click the asterisk (*) at the top of the list of table fields in the table window. This figure shows the query design grid with all the fields added.

6. To limit the query results based on field values, in the query design grid, in the Criteria row, enter the criteria that you want to use to limit the results. 

Table of example criteria

The following table shows some example criteria and explains the effect that they have on the results of a query. 

[NOTE Many of the examples in this table use wildcard characters to make the query more flexible or powerful.]

7. On the Design tab, in the Results group, click Run. 

8. Verify that the query returns the records that you want to update. 

9. To remove any fields that you do not want included in the query design, select the fields and then press DELETE. 

10. To add any fields that you want to include in the query design, drag the additional fields to the query design grid.

STEP 2: UPDATE THE RECORDS

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

This procedure shows you how to change a select query to an update query. When you do this, Access adds the Update to row in the query design grid. The following illustration shows an update query that returns all the assets purchased after January 5, 2005 and changes the location to "Warehouse 3" for all the records that meet that criterion.

2. Locate the field that contains the data that you want to change, and then type your expression (your change criteria) in the Update to row for that field. 

You can use any valid expression in the Update to row.

3. On the Design tab, in the Results group, click Run. 

An alert message appears.

4. To run the query and update the data, click Yes. 

NOTE When you run the query, you might notice that some fields are missing from your result set. If your query contains fields that you don't update, Access does not display those fields in the results, by default. For example, you might include ID fields from two tables to help ensure that your query identifies and updates the correct records. If you don't update those ID fields, Access does not display them in the results.

CREATE AND RUN THE UPDATE QUERY

[NOTE The following steps assume the use of the two preceding sample tables. You can adapt the steps to fit your data.]

1. On the Design tab, in the Macros & Code group, click Query Design. 

2. In the Show Table dialog box, click the Tables tab. 

3. Double-click your source and destination tables to add them to the query, and then click Close. Each table appears in a window in the query designer. 

4. In most cases, Access automatically joins related fields in a query. To manually join fields that contain related information, drag the related field from one table to the equivalent field in the other table. 

For example, if you use the sample tables shown earlier, you drag the Client ID field to the Customer ID field. Access creates a relationship between those fields in the two tables and uses that relationship to join any related records.

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

6. In the destination table, double-click the fields that you want to update. Each field appears in the Field row in the query design grid. 

If you use the sample tables, you add all fields except the Customer ID field. Notice that the name of the destination table appears in the Table row of the design grid.

7. In the Update To row of the query, in each of the columns that contains a destination field, add the name of the source table and the field in the source table that corresponds to the field in the destination table, and make sure that you use this syntax: [Table].[Field], where you enclose table and field names with square brackets, and you separate table and field names with a period. 

  This figure shows part of the design grid, using the sample tables. Note the syntax for the table and field names in the Update To row.

  As you continue, remember that you must spell the table and field names in the Update To row correctly, and you must match any punctuation in the original table and field names. However, you do not have to match capitalization.

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

9. When asked to confirm the update, click Yes.