Contents
Creating a Query with the Simple Query Wizard
EXTRA FOR EXPERTS
Step-by-Step 3.1
Sorting Data
Step-by-Step 3.2
Filtering Data
Step-by-Step 3.3
Chapter :Lesson 3: Creating Queries:Creating a Query in Design View
Creating a Query in Design View
Step-by-Step 3.4
Moving and Sorting Fields in Design View
TIP
Step-by-Step 3.5
Running a Query
Step-by-Step 3.6
Adding a Condition to a Field
Step-by-Step 3.7
Chapter :Lesson 3: Creating Queries:Creating Table Relationships
Creating Table Relationships
Step-by-Step 3.8
Viewing Related Records
Step-by-Step 3.9
Chapter :Lesson 3: Creating Queries:Creating a Multitable Query
Creating a Multitable Query
EXTRA FOR EXPERTS
Step-by-Step 3.10
Chapter :Lesson 3: Creating Queries:Using Operators in a Condition
Using Operators in a Condition
Chapter :Lesson 3: Creating Queries:Calculating Data
Calculating Data
Using the Total Row
Step-by-Step 3.12
Creating a Calculated Field in a Query
EXTRA FOR EXPERTS
Step-by-Step 3.13
Step-by-Step 3.14
Chapter :Lesson 3: Creating Queries:End of Chapter Review
End of Chapter Review
Lesson 3: Creating Queries: Summary
Lesson 3: Creating Queries: Vocabulary Review
Lesson 3: Creating Queries: Review Questions
TRUE / FALSE
WRITTEN QUESTIONS
FILL IN THE BLANK
3: Creating Queries: Projects
PROJECT 3–1
PROJECT 3–2
PROJECT 3–3
PROJECT 3–4
Lesson 3: Creating Queries: Critical Thinking
ACTIVITY 3–1
ACTIVITY 3–2
ACTIVITY 3–3
Creating a Query with the Simple Query Wizard
A query is a database object that lets you ask the database about the data it contains. The result of a query is a datasheet that includes the records you asked to see. You can use a query to see all orders placed after a certain date or all customers who live in a certain zip code. When you specify a certain date or zip code in a query, these specifications are called conditions. A condition (also called a criterion) is a way of telling the query which data you are interested in seeing. For example, when you ask to see customers living in a certain zip code, the zip code 78001 is a condition. When the condition has two or more parts to it, such as customers who have ordered a specific part and who live in a certain zip code, the two conditions are called criteria. You can also create a query that doesn't contain any conditions, but still displays any or all of the fields that you want to see.
A query is based on a table (or on another query), and some queries are based on more than one table (or query). When you say that a query is based on a table, it means that the data in the query datasheet is really data that is stored in a table. When you open a query object, you run the query. Running a query displays a datasheet that is similar in appearance to the datasheet you see when you open a table. However, the query uses the conditions to display only the records and fields that you asked to see. When you run a query, the data in the table on which the query is based still exists in the table. A query is just another way of viewing the table's data.
An easy way to create a query is to use the Simple Query Wizard, which asks you what data you want to see by letting you select options in dialog boxes. To start the Simple Query Wizard, click the Create tab on the Ribbon. The Create tab contains options for creating different database objects. In the Queries group on the Create tab, click the Query Wizard button. The New Query dialog box opens, as shown in Figure 3–1.
FIGURE 3–1 New Query dialog box
Make sure that the Simple Query Wizard option is selected, and then click OK. The first Simple Query Wizard dialog box opens, as shown in Figure 3–2. You use the Tables/Queries arrow in this dialog box to select the table (or query) that contains the data you want your new query to display. After selecting the table (or query) on which to base your new query, you click a field in the Available Fields list box, and then click the Select Single Field button to add one field at a time to the new query. To add all fields to the new query, click the Select All Fields button. When you add a field to a query, the field moves from the Available Fields list box to the Selected Fields list box.
EXTRA FOR EXPERTS
The Tables/Queries list box displays all the table and query objects in the database. The table objects are listed first (in alphabetical order), followed by the query objects (also in alphabetical order). To select a table or query in the list, click the Tables/Queries arrow.
FIGURE 3–2 First Simple Query Wizard dialog box
When you click Next, the second Simple Query Wizard dialog box gives you the option of creating a detail query or a summary query. A detail query shows every field in each record. A summary query lets you summarize relevant data, such as adding the field values in a column that stores price data. Access gives you the choice of creating a summary query only when the data you selected could be used in calculations.
In the last Simple Query Wizard dialog box, Access suggests a title for your query by using the object name on which the query is based, plus the word “Query,” as shown in Figure 3–3. You can change the default query title or use the one Access suggests. When you click Finish, the query datasheet is displayed.
FIGURE 3–3 Final Simple Query Wizard dialog box
Step-by-Step 3.1
1. Start Access. Open the Product.accdb database from the Access Lesson 03 folder where your Data Files are stored.
2. If the Security Warning appears below the Ribbon, click the Enable Content button.
3. On the Ribbon, click the Create tab.
4. In the Queries group on the Create tab, click the Query Wizard button. The New Query dialog box opens, as shown in Figure 3–1.
5. Make sure Simple Query Wizard is selected, and then clickOK. The first Simple Query Wizard dialog box opens, as shown in Figure 3–2. Table: Orders is selected in the Tables/Queries list box because it is the first table in the alphabetical list of tables in the database.
6. Click the Tables/Queries arrow, and then click Table: Products. The fields in the Products table appear in the Available Fields list box.
7. In the Available Fields list box, click Product Name, and then click the Select Single Field button
. The Product Name field moves to the Selected Fields list box, which adds this field to the query.
8. In the Available Fields list box, click Retail Price, and then click the Select Single Field button
. The Retail Price field moves to the Selected Fields list box. The Retail Price field is the second field added to the query.
9. Click Next. The second Simple Query Wizard dialog box asks if you want to create a detail query or summary query. Make sure the Detail option button is selected.
10. Click Next. The final Simple Query Wizard dialog box asks you for a title, as shown in Figure 3–3. The default query title is Products Query, which is the name of the table on which the query is based, plus the word Query.
TIP
The title you give to a query is also used as the query object name.
11. Select Products Query in the text box, and then type Price List as the new query title.
12. Make sure that the Open the query to view information option button is selected.
13. Click Finish. The query datasheet opens, as shown in Figure 3–4. The datasheet contains the Product Name and Retail Price fields for 48 records from the Products table. Leave the query open for the next Step-by-Step.
FIGURE 3–4 Query datasheet
Sorting Data
When you view a table or query datasheet, the records might not appear in the order that you would like to see them listed. For example, you might want to list customers in alphabetical order or list prices in order from least expensive to most expensive. When you view field values in ascending or descending order from A to Z or from smallest to largest, you apply a sort to the field. Sorting a field in ascending order arranges records from A to Z, or from smallest to largest. Sorting a field in descending order arranges records from Z to A, or from largest to smallest. An easy method to change the way data is sorted is to click any field value in the field you want to sort, and then click the Ascending or Descending buttons in the Sort & Filter group on the Home tab.
Step-by-Step 3.2
1. In the Retail Price column, click the value in the first row ($6.59). The Retail Price field is selected.
2. On the Ribbon, click the Home tab.
EXTRA FOR EXPERTS
When a field is sorted, an arrow appears on the field selector to indicate the way records are sorted. In Figure 3–5, the Retail Price field selector has a small down arrow to indicate a descending sort order. An ascending sort order displays a small up arrow on the field selector.
3. In the Sort & Filter group, click the Ascending button
. The records are sorted in ascending order by retail price, with the record for the least expensive item, a clipboard priced at $1.29, at the top of the datasheet.
4. In the Sort & Filter group, click the Descending button
. The records are sorted in descending order by retail price, as shown in Figure 3–5, with the record for the most expensive item, a laptop computer priced at $854.22, at the top of the datasheet. Leave the query open for the next Step-by-Step.
FIGURE 3–5 Records sorted in descending order by retail price
Filtering Data
When you are viewing a table or query datasheet, you might want to display records that contain a certain value, such as products that have a retail price of $9.99. You can use a filter to view the data in this way. A filter temporarily displays records in a datasheet based on the condition that you specify. You can think of a filter as “filtering out” the records that do not match the condition.
You can use different types of filters to display the data you need. When you use Filter By Selection, you select a field value (such as Oak Office Desk), or part of a field value (such as the just letter D) in a datasheet, and then click the Selection button in the Sort & Filter group on the Home tab. A menu opens with a list of options for filtering the field. For numerical data, the options let you filter records that have the same field value as the one you selected, field values that do not equal the selected field value, field values that are less than or equal to or greater than or equal to the selected field value, and in other ways. For fields defined with the Text data type, the options let you filter records that have the same field value, have different field values, contain the field value, or do not contain the field value. Clicking an option in the menu displays only those records in the datasheet that match the filter condition.
You can use Filter By Form when you need to display records that contain one or more values based on the values stored in one or more fields. To use Filter By Form, click the Advanced button in the Sort & Filter group on the Home tab. In the menu that opens, click Filter By Form. The datasheet temporarily hides all the records it contains and displays a list box for a selected field, as shown in Figure 3–6. Clicking an arrow in a field displays the field's values in a list. When you click a value in the list, you set the filter. Click the Toggle Filter button in the Sort & Filter group on the Home tab to display only the records in the datasheet that match the filter. You can set the filter for one or more fields in the datasheet.
FIGURE 3–6 Price List datasheet after selecting Filter By Form
An easy way to sort and filter data using the same options available in the Sort & Filter group is to use an AutoFilter. An AutoFilter is a menu that opens when you click the arrow on the right side of a field selector. The menu contains options for sorting data and clearing any filters that you have already applied. It also contains options for using Filter By Selection and Filter By Form. Figure 3–7 shows the AutoFilter that opens when you click the arrow on the Product Name field selector and then point to Text Filters. This menu shows the Filter By Selection options for the Product Name field, which has the Text data type.
FIGURE 3–7 AutoFilter for the Product Name field (a Text field)View PDF
Figure 3–8 shows the AutoFilter that opens when you click the arrow on the Retail Price field selector, and then point to Number Filters. This menu shows the Filter By Selection options for the Retail Price field, which has the Number data type.
FIGURE 3–8 AutoFilter for the Retail Price field (a Number field)
After applying a filter to a field, clicking the Toggle Filter button in the Sort & Filter group on the Home tab removes the filter and displays all records in the datasheet again. To delete a filter from a query, click the Advanced button, and then click Clear All Filters.
Step-by-Step 3.3
1. Make sure the Retail Price field in the datasheet is sorted in descending order (see Figure 3–5). In the datasheet, click the second value in the Product Name field (Oak Office Desk).
2. In the Sort & Filter group on the Home tab, click the Selection button, and then click Contains “Oak Office Desk”. The filter is applied and one record is displayed in the datasheet.
3. In the Sort & Filter group, point to the Toggle Filter button. The Toggle Filter button has a “Remove Filter” ScreenTip because clicking it will remove the filter. Click the Toggle Filter button. The filter is removed and all records are displayed.
4. In the Product Name field in the second row in the datasheet, double-click Desk to select the word Desk.
5. Click the Selection button, and then click Contains “Desk”. Four records that contain the word Desk anywhere in the Product Name field are displayed, as shown in Figure 3–9.
EXTRA FOR EXPERTS
You can identify a field that has a filter applied to it by looking at the field selector. Figure 3–9 shows a filter icon on the Product Name field selector, indicating a filter has been applied. To remove the filter, click the Toggle Filter button in the Sort & Filter group on the Home tab.
FIGURE 3–9 Using Filter By Selection to display records that contain the word “Desk”
6. Click the Toggle Filter button. The filter is removed, the filter icon is removed from the Product Name field selector, and the datasheet displays all 48 records. Even though you removed the filter from the records, you need to clear the filter to delete it from the query.
7. In the Sort & Filter group on the Home tab, click the Advanced button, and then click Clear All Filters.
8. In the Sort & Filter group on the Home tab, click the Advanced button, and then click Filter By Form. The data in the datasheet is hidden and an arrow appears in the first row in the Product Name field.
9. Click in the first row in the Retail Price field. An arrow appears in the first row for the Retail Price field. (See Figure 3–6.)
10. Click the arrow on the Retail Price field. The list that opens displays all of the field values in the Retail Price field. In the list, click 9.99.
theToggle Filter button. The Toggle Filter button has an “Apply Filter” ScreenTip because clicking the button will apply the filter. Click the Toggle Filter button. Two records are displayed in the datasheet, both containing the value $9.99 in the Retail Price field.
12. Click the Toggle Filter button. The filter is removed and all 48 records appear in the datasheet.
13. Click the arrow on the Retail Price field selector. The AutoFilter opens. In the AutoFilter, click Sort Smallest to Largest. The values in the Retail Price field are sorted in order from smallest to largest.
14. Click the Close ‘Price List’ button
to close the query. Click Yes to save the query. Leave the database open for the next Step-by-Step.
Chapter :Lesson 3: Creating Queries:Creating a Query in Design View
Creating a Query in Design View