Access Lesson 3 Creating Queries

Access Lesson 3Creating Queries Microsoft Office 2010 Introductory Pasewark & Pasewark

Objectives • Create a query using a Wizard. • Sort and filter data in a datasheet. • Create a query in Design view. • Create relationships in a database. • Create a query based on more than one table. • Use operators in a condition in a query. • Calculate data using a query. 2 2

And operator AutoFilter calculated field common field condition expression filter Filter By Form Filter By Selection foreign key multitable query one-to-many relationship Or operator Vocabulary 3 3

query referential integrity relationship Simple Query Wizard sort subdatasheet Total row Vocabulary (continued) 4 4

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. • Specifications in a query are called conditions. • A condition is also called a criterion. • When the condition has two or more parts to it, the two conditions are called criteria. 5 5

Creating a Query with the Simple Query Wizard (continued) • A query is based on a table or another query (or multiple tables/queries) • When you open a query object, you run the query. Running a query displays a datasheet with only the records and fields that you asked to see. • The Simple Query Wizard is an easy way to create a query. 6 6

Creating a Query with the Simple Query Wizard (continued) • First Simple Query Wizard dialog box 7 7

Creating a Query with the Simple Query Wizard (continued) • 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. 8 8

Sorting Data • 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. 9 9

A filter temporarily displays records in a datasheet based on the condition that you specify. You can use different types of filters to display the data you need. When you use Filter By Selection, you select a field value, and then click the Selection button in the Sort & Filter group on the Home tab. Filtering Data 10 10

You can use Filter By Form to display records that contain one or more values based on the values stored in one or more fields. Click the Advanced button in the Sort & Filter group on the Home tab. Click the Toggle Filter button in the Sort & Filter group on the Home tab to display only records in the datasheet that match the filter. Filtering Data (continued) 11 11

An easy way to sort and filter data 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, and contains options for: Sorting data and clearing filters. Using Filter By Selection and Filter By Form. Filtering Data (continued) 12 12

Filtering Data (continued) • AutoFilter for the Product Name field (a Text field) 13 13

Creating a Query in Design View • For a query datasheet, you have more sorting and filtering options when you create or modify a query in Design view. • In the Query Design window, you build and change the query using the design grid. 14 14

Creating a Query in Design View (continued) • The Query window in Design view is divided into two parts. • The top shows the field list for the table you included in the query design. • The bottom contains a design grid that allows you to specify fields, conditions, and sort orders. • A query can contain one, some, or all of the fields in the table. You can add the fields in any order to the design grid.

Creating a Query in Design View (continued) • You can set a sort order for a field using the field's Sort box in the design grid. • You can run a query by clicking the Run button in the Results group on the Query Tools Design tab. When you run a query, the results appear in a query datasheet. • To add a condition to a field, click in the field's Criteria box, and then type the condition. 16 16

Creating Table Relationships • The feature that lets you connect the data in the tables is a relationship. • To create a relationship between two tables, you must design the tables so they contain a common field. A common field is a field that appears in both tables, has the same data type, and contains the same values. • A common field is also called a matching field. 17 17

Creating Table Relationships (continued) • The most common relationship is a one-to-many relationship. • One record in the first table can match many records in the second table. • The common field in the related table is called a foreign key when it is used in a relationship.

Creating Table Relationships (continued) • When you relate tables, Access uses a set of rules to ensure that there are matching values in the common field used to form the relationship. This set of rules is called referential integrity. • Referential integrity protects the data in the tables to make sure that data is not accidentally deleted or changed, resulting in inconsistent data. 19 19

Creating Table Relationships (continued) • Relationships window after creating a one-to-many relationship 20 20

Creating a Multitable Query • Queries based on more than one table are sometimes called multitable queries. • After you add two related tables to the query design, a join line shows the relationship. • The join line connects the common field used to relate the tables. • It defines the type of relationship by using the "1" to represent the "one" and the infinity symbol to represent the "many" side of the relationship.

Using Operators in a Condition • In an exact match condition the records must contain the specified value. • In a range-of-values condition the record must match a range of values. • The And operator selects records that match all of two or more conditions in a query. • The Or operator selects records that match at least one of two or more conditions in a query. 22 22

Using Operators in a Condition (continued) • Relational operators

Calculating Data • Access provides two ways to calculate data using a query: • Total Row: Use the Total row to count the number of values in a column. The Total row includes additional functions for values. • Calculated Field: A field with a value calculated using other fields is called a calculated field. The calculation is called an expression.

Summary In this lesson, you learned: • A query is a database object that lets you ask the database a question about the data it contains. You can create a query quickly and easily using the Simple Query Wizard, which asks you about the data you want to see and lets you select options in dialog boxes. • You can change the way data is sorted in a datasheet by applying an ascending or a descending sort order to one of the fields. 25 25

Summary (continued) • You can use a filter in a datasheet to temporarily display records in a datasheet based on a condition that you specify. Filter By Selection lets you select a field value or part of a field value in a datasheet and then filter out all records that do not match the filter. Filter By Form lets you display records that match a value you select in a field. An AutoFilter opens when you click the arrow on a field selector. You can use an AutoFilter to sort and filter data. You can also move and sort fields in Design view. To run a query, click the Run button in the Results group on the Query Tools Design tab. 26 26

Summary (continued) • When you need to create a query that uses conditions to select records, create the query in Query Design view. • Use the Relationships window to create relationships between tables in a database by joining tables with a field that contains matching field values. A one-to-many relationship exists when one record in the primary table matches zero, one, or many records in the related table. Referential integrity is the set of rules that Access uses to protect data in the tables and to make sure that data is not accidentally deleted or changed. 27 27

Summary (continued) • A multitable query is a query that is based on more than one table. • When you need to use a query to search for records that match a range of values, use a relational operator in the query design. When you need to select records that match all of two or more conditions in a query, use the And operator by placing the criteria in the same Criteria row in the design grid. When you need to select records that match at least one of two or more conditions in a query, use the Or operator by placing the first condition in the Criteria row and the second condition in the or row in the design grid.

Summary (continued) • In Access, you can perform calculations by using the Total row in a datasheet, or by creating a calculated field in the design grid in Query Design view.

Access Lesson 3 Creating Queries