Le Moyne College CSC-151 Microsoft Access 2003 Reference

Query design view

A query specifies criteria that indicates which data fields and data rows are displayed to you. While this is similar in capabilities to the Filter by Form discussed in the datasheet view, a query can be created that combines data from two or more tables.

To create a query object in Microsoft Access you first click on the Query button in the list of object types. As shown in the picture to the right, clicking on the Query object type displays two methods for creating a new query object: Create query in Design view Create query by using wizard In this class, we will learn both methods for creating a query.

Create query by using wizard

In the database window, double-click on the "Create query by using wizard" entry to start the wizard.

	Query Wizard Step 1 In the first step of the Query Wizard, you must select the fields you want to display on your query results. Select a table or query from the Tables/Queries drop down list. Next you want to move fields from the Available Fields list to the Selected Fields list. You may either: Select one or more fields from the Available Fields list, and then click on the right arrow button. Click on the double-right arrow button to move all fields from the Available Fields list to the Selected Fields list. If you make a mistake, you can select a field in the Selected Fields list, and then click the left arrow button to move it back to the Available Fields list. You can also click on the double-left arrow button to move all fields from the Selected Fields list to the Available Fields list. Click the Next button once you've selected the fields you want to include in your query.
In this demonstration, the First Name, Last Name, City, and Home Phone Number fields in the Student table were moved to the Selected Fields list.
	Query Wizard Step 2 (last step) In the second step of the Query Wizard: You identify the name associated with your query. You indicate whether the query results should be displayed (view information) or in design view (to allow you to modify its design). The question "What title do you want for your query?" is misleading. In this wizard, the word "title" means name.

In this demonstration, the "title" was changed to StudentQuery and the option to display the results (view information) was selected. The datasheet view displayed at this time looks like the picture on the right: Please see the query datasheet view for information on this view.

Create query in Design view

In the database window, double-click on the "Create query in Design view" entry to create a query using the design view.

Show Table Before the query design view is displayed, you must select one or more tables or queries to use as the basis for this new query. At the moment, the database only contains one table - Student - so this is already selected in the list of tables. Clicking the Add button will add the Student table to the query design view, which is currently displayed behind the Show Table dialog box. At this point, the Close button may be clicked to close the Show Table dialog box.

The query design view displayed at this time looks like the picture on the right: The query design view contains two sections. Top section - identifies the tables and queries being used by this query. Bottom section - identifies the criteria specified for this query. An explanation of each row shown in the bottom section follows. Field: identifies a field that is being used in this query. Table: identifies the table that contains the field. Sort: indicates whether the query results will be sorted by this field. Options are ascending or descending. Show: indicates whether the query results will display the data for this field. A checkmark means the field data will be displayed in the query results. Criteria: identifies the data selection criteria specified for this query. or: identifies additional data selection criteria specified for this query.

Create query in Design view examples

Each of the following examples assumes that you've double-clicked on the "Create query in Design view" entry in the database window and that you added the Student table to the query design view.

1. Show First Name, Last Name, City, and Home Phone Number for all Students.
1. In the top section of the query design view, double-click on the First Name field in the Student table picture.

The First Name field will appear in the bottom section of the query design view in the first column.

2. In the top section of the query design view, double-click on the Last Name field in the Student table picture.

The Last Name field will appear in the bottom section of the query design view in the second column.

3. In the top section of the query design view, scroll down until you see the City field, and then double-click on the City field in the Student table picture.

The City field will appear in the bottom section of the query design view in the third column.

4. In the top section of the query design view, scroll down until you see the Home Phone Number field, and then double-click on the Home Phone Number field in the Student table picture.

The Home Phone Number field will appear in the bottom section of the query design view in the fourth column.

At this point, the query design view looks like the following:

5. Click on the Run button to execute the query and display the results.

The data resulting from executing this query is displayed in a datasheet view. This datasheet view would look like:

6. Clicking on the View button will switch back to the query design view.
7. At this point, you can click on the Save button if you want to save this query. At this point you would give this query object a name. This query name would then be listed as a query object on the database window.
2. Show First Name, Last Name, City, and Home Phone Number for all Students that live in Syracuse and whose First Name begins with M.
1. Add the First Name, Last Name, City, and Home Phone Number fields to the bottom section of the query design view as described in example A above.
2. In the bottom section of the query design view, click on the Criteria cell in the City column. Type Syracuse into this criteria cell.
3. In the bottom section of the query design view, click on the Criteria cell in the First Name column. Type M* into this criteria cell.
4. Strike the tab key on the keyboard. At this point, the bottom section of the query design view should look like:
5. Click on the Run button to execute the query and display the results.

The data resulting from executing this query is displayed in a datasheet view. This datasheet view would look like:

Comparing these results with example A, we see that only those students that live in Syracuse AND whose first name begins with an M are shown.

6. Clicking on the View button will switch back to the query design view.
7. At this point, you can click on the Save button if you want to save this query. At this point you would give this query object a name. This query name would then be listed as a query object on the database window.
3. Show First Name, Last Name, City, and Home Phone Number for all Students that live in Piscataway or whose First Name begins with M.
1. Add the First Name, Last Name, City, and Home Phone Number fields to the bottom section of the query design view as described in example A above.
2. In the bottom section of the query design view, click on the Criteria cell in the City column. Type Piscataway into this criteria cell.
3. In the bottom section of the query design view, click on the Or cell in the First Name column. Type M* into this criteria cell.
4. Strike the tab key on the keyboard. At this point, the bottom section of the query design view should look like:
5. Click on the Run button to execute the query and display the results.

The data resulting from executing this query is displayed in a datasheet view. This datasheet view would look like:

Comparing these results with example A, we see that only those students that live in Piscataway OR whose first name begins with an M are shown.

6. Clicking on the View button will switch back to the query design view.
7. At this point, you can click on the Save button if you want to save this query. At this point you would give this query object a name. This query name would then be listed as a query object on the database window.
4. Show First Name, Last Name, City, and Home Phone Number for all Students that live in either Syracuse or Piscataway.
1. Add the First Name, Last Name, City, and Home Phone Number fields to the bottom section of the query design view as described in example A above.
2. In the bottom section of the query design view, click on the Criteria cell in the City column. Type Syracuse into this criteria cell.
3. In the bottom section of the query design view, click on the Or cell in the City column. Type Piscataway into this criteria cell.
4. Strike the tab key on the keyboard. At this point, the bottom section of the query design view should look like:
5. Click on the Run button to execute the query and display the results.

The data resulting from executing this query is displayed in a datasheet view. This datasheet view would look like:

Comparing these results with example A, we see that only those students that live in Syracuse OR Piscataway are shown.

6. Clicking on the View button will switch back to the query design view.
7. At this point, you can click on the Save button if you want to save this query. At this point you would give this query object a name. This query name would then be listed as a query object on the database window.

Query datasheet view

The datasheet view used to display query data has the same features and capabilites as the datasheet view used to view table data.

• Go here for a basic description of the datasheet view.
• Go here for a description of the advanced features of the Datasheet view.

Query SQL view

This view displays the Structured Query Language (SQL) statement that Microsoft Access generated based on your query design.

When you run a query, Microsoft Access actually executes the SQL statement displayed in this view. The results produced by executing the SQL statement are then displayed in a datasheet view.

For those of you that are curious, here are the SQL statements generated by Microsoft Access for the four query examples (A-D) described above.

1. SELECT Student.[First Name], Student.[Last Name], Student.City, Student.[Home Phone Number]
   FROM Student;
2. SELECT Student.[First Name], Student.[Last Name], Student.City, Student.[Home Phone Number]
   FROM Student
   WHERE (((Student.[First Name]) Like "M*") AND ((Student.City)="Syracuse"));
3. SELECT Student.[First Name], Student.[Last Name], Student.City, Student.[Home Phone Number]
   FROM Student
   WHERE (((Student.City)="Piscataway")) OR (((Student.[First Name]) Like "M*"));
4. SELECT Student.[First Name], Student.[Last Name], Student.City, Student.[Home Phone Number]
   FROM Student
   WHERE (((Student.City)="Syracuse")) OR (((Student.City)="Piscataway"));