Access labs 2017

Lesson

4

Lesson 4 - Working With Queries

Lesson Topics:

4.1 Working in Query Design View

4.2 Creating a Query in Design View

4.3 Adding Fields to a Query

4.4 Removing Fields from a Query

4.5 Saving a Query

4.6 Running a Query

4.7 Adding Criteria to a Query

4.8 Specifying Multiple Criteria

4.9 Sorting Data in a Query

4.10 Moving Columns in a Query

4.11 Using the Query Wizard

4.1 Working in Query Design View

In this lesson, you will learn about the Query object.

I

n the last lesson, you learned how to filter data in a table, using the Filter by Selection and Filter by Form tools. However, for more complex searches, it is often better to create a query. Queries are also the fastest and easiest way to retrieve information from a database. XE "Queries:design view" XE "Queries:parts of"

A query allows you to ask a question of your data. For instance, you might want to know how many customers in the state of Illinois spent more than $250 in the year 2009. The fields you wish to receive along with the criteria by which you wish to limit your data are set in Query Design View.

The parts of a query in Design View are as follows:

Field Lists – the tables along with their fields that are part of the query. This section is the top part of the query.

Design grid – The lower part of the query broken down into rows and columns. This is where you add fields from the field lists that you want to be part of your query.

· Field – The table field whose data will be displayed when the query is run.

· Table – The table that contains the field.

· Sort row – designates how the results of the query are to be sorted.

· Show row – designates which fields will be displayed when the query is run.

· Criteria row – used to specify the limits placed on the records to be retrieved.

To Open a Query in Design View

1. Select Queries in the Navigation Pane.

2. Right-click the query you wish to open and select Design View from the contextual menu.

Or

Click the View button arrow and then select Design View from the menu

Or

From query Datasheet view, click the Design View button on the lower right-hand corner of the Access screen.

3. To close the query, click the query’s close button.

Let’s Try It!

What	Why
1. Click the File tab on the Ribbon button.	Displays Backstage view.
2. Click Open.	Displays the Open dialog box.
3. Click Browse in the center pane.	Displays the Computer pane.
4. Click Desktop in the left pane double-click the Lesson Files folder in the right pane.	Displays the contents of the Lesson Files folder.
5. Select the Video Sales 4 file and then click Open.	Opens the Video Sales 4 database.
6. If necessary, select Queries from the Navigation Pane drop-down list as shown below.	Displays query objects.
7. Right-click the qryProducts query.	Displays the contextual menu.
8. Select Design View.	Opens the query in Design View.
9. Observe the criteria row for the Category field.	The results of the query are limited to those records whose category is “Comedy”
10. Press the Ctrl + W keystroke combination.	Closes the query.

4.2 Creating a Query in Design View

In this lesson, you will learn how to create a query in Design View.

T

here are two ways to create queries: using the query wizard or manually creating a query in Design View. In this lesson, we will create a query in Design View that returns data from a single table. This is accomplished by clicking the Query Design button under the Create tab, and then choosing which table you wish to add to the query. XE "Queries:creating new in design view"

To Create a New Query in Design View

1. Click the Create tab on the Ribbon.

2. Click the Query Design button in the Queries command set.

3. Click the Tables tab in the Show Table dialog box.

4. Select the table upon which you want to base your query.

5. Click Add on the bottom of the Show Table window.

6. Click Close when finished.

Let’s Try It!

What	Why
1. Click the Create tab on the Ribbon.	Displays commands for creating Access objects.
2. Click the Query Design command button.	Displays the Show Table dialog box. This lists all tables and queries in your database.
3. Click tblCustomers.	Selects the table we wish to add to our query.
4. Click Add.	Adds tblCustomers to the Field Lists area (the top portion) of our query.
5. Click Close.	Closes the Show Table dialog box and displays query Design View.

4.3 Adding Fields to a Query

In this lesson, you will manually add fields to your query.

o

nce you have chosen the table to use in your query, you will next need to add the fields to your query that you wish to be included in the query’s results. There are several ways to add fields to your query: XE "Queries:adding fields to"

· Click and drag the field from the Field Lists area to the design grid.

· Double-click the field in the table list.

· Click in the field row, click the arrow that appears and then select the desired field.

· To add all fields, double-click the title bar of the field list box, click anywhere within the field list, and drag to the query grid.

· Click the asterisk in the field list box to add all fields to the query results (the individual fields in the query grid are not displayed).

To Add Fields to a query

1. Double-click the field name in the field list box

Or

Click and drag the field from the field list box to the design grid

Or

Click the field row and then choose the desired field by clicking the drop-down arrow.

To Add All of the Fields in a Table to a Query

1. Double-click the title bar of the field list box.

2. Click and hold your left mouse button anywhere within the field list box.

3. Drag to the query grid.

Let’s Try It!

What	Why
1. Double-click FirstName in the field list box.	Adds the FirstName field to the first column of the query grid.
2. Click the LastName field in the field list box and drag it to the second column of the query grid as shown below.	Drags the LastName field to the second column of the query grid.
3. Press Tab.	Moves the insertion point to the field row in the next blank column.
4. Click the arrow and then choose Address from the drop-down list as shown below.	Adds the address field to the third column of the query gird.
5. In the field list box (on the top section of the query), scroll downwards until the City, State, and Zip fields are visible.	Scrolls downward in the field list box to display the additional table fields.
6. Click the City field in the field list box.	Selects the field we wish to add to our query.
7. Press and hold the Ctrl key and click the State and Zip fields in the field list box.	Chooses the additional fields to be added to the query. Holding down the Ctrl key allows you to select more than one field.
8. With the Ctrl key still held down, click anywhere in the selected fields and then drag to the fourth blank column in the query grid as shown below. Release the mouse button and the Ctrl key.	Adds the City, State and Zip fields to the query.
9. Double-click the HomePhone field.	Adds the HomePhone field to the query grid.

4.4 Removing Fields from a Query

In this lesson, you will learn how to remove fields from the query grid.

A

fter you have added fields to your query, you may decide that you in fact do not want a particular field to be included in your query. To remove a field from the query grid, select the column by clicking the thin gray bar above the field row and then press the Delete key. XE "Queries:removing fields from" XE "Queries:hiding field results"

SHAPE \* MERGEFORMAT

You can also simply choose not to display a column in the query’s results by clicking the checkbox in the Show row. The field will still be part of the query, but will not be displayed in the query’s results.

To Remove a Field from a Query

1. Click the thin gray bar above the field row for the column you wish to remove.

2. Press the Delete key.

To Hide a Field from the Query Results

1. Uncheck the checkbox in the Show row for the field whose results you wish to hide.

Let’s Try It!

What	Why
1. Click the thin gray bar above the Field row for the HomePhone field as shown below.	Selects the field we wish to remove from the query grid. We have decided that we do not want to include the HomePhone field in our query.
2. Press the Delete key.	Removes the selected field from the query.

4.5 Saving a Query

In this lesson, you will learn how to save a query.

I

f you will need to generate the results for a query more than once, you may wish to Save the query, rather than recreating it every time you need it. To save a query, click the Save button on the Quick Access Toolbar and then enter the name for your query in the Query Name box. Don’t forget about naming conventions! XE "Queries:saving"

SHAPE \* MERGEFORMAT

Saving a Query

To Save a Query

1. Click the Save button on the Quick Access toolbar.

Or

Click the File tab on the Ribbon and click Save.

2. Enter the name for the query in the Query Name box.

3. Click OK.

Let’s Try It!

What	Why
1. Click the Save button on the Quick Access toolbar as shown below.	Displays the Save As dialog box as we have not yet saved our query.
2. Type: qryCustomers in the Query Name box as shown below.	Enters a name for our query.
3. Click OK.	Saves the query as qryCustomers and then closes the Save As dialog box.

4.6 Running a Query

In this lesson, you will learn how run a query.

W

e already know that a query is a question that you ask about the data in your database. To receive the answer to your question, you need to run your query. The answer to a query is called a recordset or a result set. It is also commonly referred to as a dynaset. In this class, we will refer to it as a result set. XE "Queries:result set" XE "Queries:running" XE "Run Query Icon"

The Query Result Set

To run a query if you are in Design View, click the run icon on the Ribbon or click the View button on the Ribbon. To run a query that is not already open, double-click the query name in the Navigation Pane.

To Run a Query from Design View

1. Click the Run Query button on the Design tab of the Ribbon

Or

Click the View button on the Ribbon.

To Run a Query from the Navigation Pane

1. Select Queries from the Navigation Pane drop-down menu.

2. Double-click the name of the query that you wish to run.

Let’s Try It!

What	Why
1. Click the Run icon on the Ribbon.	Runs the query and displays the result set.
2. Click the View button on the Ribbon.	Switches back to Design View.
3. Click the View icon on the Ribbon.	Runs the query and displays the result set.
4. Press the Ctrl + W keystroke combination.	Closes the query.
5. Double-click qryProducts.	Opens qryProducts in Datasheet View (in other words, we run the query).
6. Press the Ctrl + W keystroke combination.	Closes the qryProducts query.

4.7 Adding Criteria to a Query

In this lesson, you will learn how to add criteria to a query.

T

he criteria row of the query grid is where you enter the limits to identify the specific records you wish to return. For example, instead of viewing all of our customers, we could enter “CA” in the criteria row for the State field. When we run the query, only customers from the state of California would be returned. XE "Queries:adding criteria to" XE "Criteria:adding to queries"

You can also use numerical expressions as your criteria. If you wanted to see all products whose cost was greater than $20, you would enter: >20 in the criteria row. Notice that numerical expressions are not surrounded in quotes whereas string (text) expessions must be enclosed in quotation marks.

To Specify Criteria for a Query

1. Click in the Criteria row of the field whose results you wish to limit.

2. Type in the criteria that must be met.

Let’s Try It!

What	Why
1. Right-click on the qryCustomers query.	Displays the contextual menu.
2. Select Design View from the contextual menu.	Opens qryCustomers in Design View.
3. In the Criteria row for the State field, type: “WI” as shown below.	Enters the criteria that must be met. In this case, we wish to see only those customers who reside in Wisconsin.
4. Click the Save button on the Quick Access toolbar.	Saves the design changes that we have made to the query.
5. Click the Run button and observe the result set as shown below.	Displays the results of the query.
The Query Results
6. Press the Ctrl + W keystroke combination. Save any changes.	Closes qryCustomers.

4.8 Specifying Multiple Criteria

In this lesson, you will learn how to add more than one limit to a query.

A

s you work more with queries, you will inevitably want to add more than one limit to a query. For instance, you may wish to see a list of all customers who live in the state of Colorado and who rented Comedy or Adventure films. XE "Criteria:adding multiple" XE "Queries:adding multiple criteria to"

To specify an OR condition for the same field, separate each limit by the Or operator. In the example below, any films with the category of Comedy or Adventure will be returned.

Separate each limit in the same field with the “OR” operator

To specify an OR condition for two different fields, enter each limit on a different row as in the example below.

To create a query where two or more conditions must be met, enter the criteria on the same row. For instance, you might wish to see all comedy films that cost more than $29.99. You would enter both criteria on the same criteria row, as shown below. This is referred to as an AND condition.

Both conditions must be met

To Specify Multiple Criteria in a Query

1. For OR criteria in the same field, separate each limit by the word OR.

2. For OR criteria in different fields, enter each limit on a separate criteria line.

3. To specify AND criteria, enter all limits on the same criteria line.

Let’s Try It!

What	Why
1. Open qryProducts in Design View.	Opens qryProducts in Design View so we can enter our criteria. Notice that we already have a limit of “Comedy” for the Category field.
2. Place your cursor after the word “Comedy” and then type: Or “Drama” as shown below.	Enters a multiple “Or” criteria for the Category field. Films with either a category of Comedy or Drama will be returned in the results.
3. Click the Run button.	All films with a category of Comedy or Drama are returned.
4. Click the View button on the Ribbon.	Switches back to Design View.
5. Click in the Criteria row for the Price field and type: > 29.99 as shown below.	Enters the criterion for the Price field. Now, only films with a Category of Comedy or Drama AND whose price is greater than $29.99 will be returned.
6. Press the Run icon on the Ribbon and observe the results.	Displays the results of the query.
7. Click the View icon on the Ribbon.	Switches back to Design View.
8. Highlight the criterion for the Price field, right-click and then choose Cut from the contextual menu.	Copies the limit for the Price field to the clipboard.
9. Press the down arrow key on your keyboard. Right-click and choose Paste from the contextual menu.	Pastes the limit one row below the original location as shown below.
10. Click the Run icon on the toolbar and observe the results.	Displays the results of the query. All films with either a category of Comedy or Drama or a price greater than $29.99 are returned.
11. Click the View button on the Ribbon.	Switches back to Design View.
12. Press the Ctrl + W keystroke combination. Click No when asked to save your changes.	Closes qryProducts. The criteria changes we made will not be saved.

4.9 Sorting Data in a Query

In this lesson, you will learn how to sort the results of a query.

T

o make the results of your query easier to work with, you will most likely wish to sort the query results in some logical order. To sort a query by a particular field, click in the sort field and then choose either Ascending (A-Z) or Descending (Z-A) from the drop-down list. XE "Sort:data in queries" XE "Queries:sorting"

You can sort by more than one query field. Access sorts from left to right. In the example below, the data would first be sorted by Category, and then by price within each category.

SHAPE \* MERGEFORMAT

Access Sorts from Left to Right

To Sort the Results of a Query

1. Click in the Sort row for the field you wish to sort.

2. Click the arrow and then choose either Ascending or Descending order.

3. To remove a sort, click in the Sort row and then choose (not sorted) from the drop-down list.

Let’s Try It!

What	Why
1. Open qryProducts in Design View.	Opens qryProducts in Design View.
2. Delete the word “Comedy” from the Criteria row for the Category field.	Removes the criteria for the Category field.
3. Click in the Sort cell for the Category field.	Displays the drop-down arrow for the Category field.
4. Click the arrow and then choose Ascending from the drop-down list as shown below.	Sets the sort order for the Category field to Ascending.
5. Click in the Sort cell for the Price field.	Displays the drop-down arrow for the Price field.
6. Click the arrow and then choose Ascending from the drop-down list.	Sets the sort order for the Price field to Ascending. The results will be sorted first by Category, and then by Price.
7. Click the Save button on the Quick Access toolbar.	Saves the query design changes.
8. Click the Run button and observe the results.	Displays the sorted query results. The data is sorted first by Category and then by Price.
9. Click the View button.	Switches back to Design View.

4.10 Moving Columns in a Query

In this lesson, you will learn how to change the order of fields in a query.

I

n the last lesson, we learned that when sorting data, Access sorts from left to right. So what if the fields are not in the order in which we need them to be? In that case, we will have to rearrange the fields in our query. XE "Queries:moving fields in"

To move a field in a query, click on the thin gray bar above the field name to select the field. Then, click the gray bar again, hold down your mouse button and drag until the field is in the desired location. As you drag, a thin black line appears, letting you know the location of the field should you release the mouse button.

Click on thin bar to select field, then click & drag to desired locationAs you drag, a black line marks the location of where the field will be placed once you release the mouse button

To Move a Column in a Query

1. Click the thin gray bar above the field name to select the field.

2. Release the mouse button.

3. Click the gray bar and hold down the mouse button.

4. Drag until the thick black line is in the desired location.

5. Release the mouse button.

Let’s Try It!

What	Why
1. Click in the Sort cell for the Price field.	Displays the drop-down arrow for the Price field.
2. Click the arrow and then choose (not sorted) from the drop-down list.	Removes the sort for the price field. We want to sort our results by Category, and then by Title. In order to do this, we will have to move the Category field to the left of the Title field.
3. Click the thin gray bar above the Category field name and then release the mouse button.	Selects the Category field.
4. Click on the thin gray bar, hold down the mouse button, and then drag to the left until the thick black line is to the left of the Title field, as shown below.	Sets the new location of the Category field.
5. Release the mouse button.	Completes the move process.
6. Click in the Sort cell for the Title field.	Displays the drop-down arrow for the Title field.
7. Click the arrow and then choose Ascending from the drop-down list.	Sets ascending as the sort order for the Title field.
8. Click the Run icon.	Displays the query results. Data is sorted first by Category, then by Title.

4.11 Using the Query Wizard

In this lesson, you will learn how to create a query using the Query Wizard.

R

ather than create your queries manually, you can use the Simple Query Wizard to help you build your query. The Simple Query Wizard steps you through the process of creating a query. You will be first prompted to choose the table and table fields upon which to base your query. Next, you select the desired view (Detail or Summary), and then provide a name for your query. That last step asks you if you want to open your query in Datasheet View or in Design View. XE "Wizards:query wizard" XE "Queries:query wizard"

To Create a Query using the Query Wizard

1. Select Queries in the Navigation Pane

2. Click the Create tab on the Ribbon.

3. Click the Query Wizard button on the Ribbon.

4. Select Simple Query Wizard and then click OK.

5. From the Tables/Queries drop-down list, choose the table or query upon which to base your new query.

6. Choose fields to add to the query by clicking the > button to add the selected field. Repeat for all desired fields.

7. To add all fields to the query, click the >> button.

8. Click Next.

9. Enter a name for your query in the “What title do you want for your query?” box.

10. Choose whether to view the query’s design (Design View) or whether to open the query to view information (Datasheet View).

11. Click Finish.

Let’s Try It!

What	Why
1. Press the Ctrl + W keystroke combination. Click Yes when asked if you want to save changes.	Saves and closes the qryProducts query.
2. Click the Query Wizard button on the Ribbon under the Create tab.	Displays the New Query dialog box.
3. Ensure that Simple Query Wizard is selected and then click OK.	Displays the first step of the query wizard.
4. Click the arrow on the right of the Tables/Queries combo box and then select tblProducts from the drop-down list as shown below.	Selects the table upon which we want to base our new query.
5. Select the Title field in the Available Fields window and then click the > button.	Selects the Title field to be added to our query.
6. Select the Acquired field in the Available Fields window and then click the > button.	Selects the Acquired field to be added to our query.
7. Click Next.	Moves to the next step of the wizard.
8. In the Title box, type: qryFilmList as shown below.	Enters a name for our query.
9. Click the Open the query to view information radio button and then click Finish.	Opens the query in Datasheet View.
10. Press the Ctrl + W keystroke combination.	Closes the qryFilmList query.
11. Press Alt + F and select Close from the File Options menu.	Closes the database.

Lesson Summary – Creating Queries

· In this lesson, you learned about the different parts of a query by looking at the query in Design view.

· Then, you learned how to create a new query in Design view by clicking the Create tab on the Ribbon, clicking the Query Design button in the Queries command set and then choosing the table or query upon which the query is to be based.

· Next, you learned how to add fields to a query by double-clicking the field name in the field box, by dragging the field from the field box to the design grid, or by choosing the field from the field row drop-down list.

· Next, you learned how to remove fields from a query by clicking the thin gray bar above the column to select it and then pressing the Delete key.

· Next, you learned how to save a query by clicking the Save button on the Quick Access toolbar and typing in the name for your new query.

· Next, you learned how to run (execute) a query by clicking the Run icon on the Ribbon or by displaying the query in Datasheet view.

· Next, you learned how to limit the results of your query by typing in the criteria that must be met in the Criteria row in the query grid.

· Next, you learned how to specify “and” criteria by entering all limits on the same line. You also learned how to specify “or” criteria, by either separating each limit with the word “OR” or by entering each limit on a separate criteria line.

· Next, you learned how to sort data in a query by clicking the Sort drop-down list for the field you wish to sort and selecting Ascending, Descending or (not sorted).

· Next, you learned how to rearrange columns in a query by clicking on the thin gray bar in the query column and dragging the column until it is in the desired position.

· Lastly, you learned how to create a query using the Simple Query Wizard by clicking on the Query Wizard button on the Ribbon and making your desired selections.

Lesson 4 Quiz

1. How can you hide columns from displaying when you run a query?

A. Uncheck the box in the Show row.

B. Uncheck the box in the View row.

C. Uncheck the box in the Field row.

D. Uncheck the box in the Sort row.

2. To display a query in Design view, you (select all that apply):

A. Double-click the query name in the Navigation pane.

B. Right-click the query name and choose Design View from the menu.

C. Select the query name and then press the “D” key on your keyboard.

D. Click the View button when in Datasheet view.

3. To create a new query in Design View, you:

A. Click “New” from the File Options Menu.

B. Click the Query Design button under the Create tab.

C. Click the New Query button under the Home tab.

D. Right-click an existing query and select “New” from the contextual menu.

4. You can base a query off of either a table or another query.

A. True

B. False

5. What are three ways that you can add fields to the query grid?

6. To select multiple non-adjacent fields from the Field List area, you:

A. Hold down the Shift key and select the desired fields from the Field List area.

B. Hold down the Alt key and select the desired fields from the Field List area.

C. Hold down the Ctrl key and select the desired fields from the Field List area.

D. You can only add one field at a time to a query.

7. How can you remove a field from the query grid?

A. Click the thin bar above the column and press the Delete key.

B. Uncheck the Show box.

C. Click the Remove button on the Ribbon.

D. Click the Field drop-down list and select “None”

8. What are two ways to execute (run) a query?

9. How can you add limits to your query so that only specific records are returned?

A. Enter your criteria in the Filter row.

B. Enter your criteria in the Limits row.

C. Select your criteria from the Criteria drop-down list.

D. Enter your criteria in the Criteria row.

10. You cannot enter more than one criteria on the same row.

A. True

B. False

11. How can you create a query using the Simple Query Wizard?

A. Click the Simple Query button under the Create tab and make your selections.

B. Click the Query button under the Design tab and make your selections.

C. Click the Query Wizard button under the Create tab and make your selections.

D. Click the File tab on the Ribbon and select New Query from the File Options menu.

12. How can you rearrange the fields in the query grid?

LAB 4 – ON YOUR OWN

1. Open the Lab4 database in the Lesson Files folder.

2. Create a new query in Design View from tblComics. Add the following fields to the query:

Title

Publisher

Original Price

YearPublished

Condition

3. Save the query as qryComicList.

4. Run the query. How many records are there?

5. Switch to Design View. Limit the query so that only those comics whose publisher is Marvel Comics or Marvel Comics Group are displayed. Run the query.

6. Switch to Design View. Move the Condition field so that it is to the right of the Title field. Sort the data in ascending order by Title.

7. Only display comics whose original price is greater than .25 (don’t forget to remove the criteria from the Publisher field). How many records do you have now? Save and Close the query.

8. Create a new query using the query wizard from tblComics that includes all of the fields. Save the query as: qryComics. Open the query in Datasheet View.

9. Switch to Design View. Set a criterion so that only those comics that were published before 1970 are displayed. Sort ascending by YearPublished. Run the query.

10. Save and close the query. Exit Microsoft Access.