Access lesson 3 creating queries
This presentation is the property of its rightful owner.
Sponsored Links
1 / 29

Access Lesson 3 Creating Queries PowerPoint PPT Presentation


  • 68 Views
  • Uploaded on
  • Presentation posted in: General

Access Lesson 3 Creating 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 .

Download Presentation

Access Lesson 3 Creating Queries

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Access lesson 3 creating queries

Access Lesson 3Creating Queries

Microsoft Office 2010 Introductory

Pasewark & Pasewark


Objectives

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


Vocabulary

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


Vocabulary continued

query

referential integrity

relationship

Simple Query Wizard

sort

subdatasheet

Total row

Vocabulary (continued)

4

4


Creating a query with the simple query wizard

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

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 continued1

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 continued2

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

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


Filtering data

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


Filtering data continued

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


Filtering data continued1

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 continued2

Filtering Data (continued)

  • AutoFilter for the Product Name field (a Text field)

13

13


Creating a query in design view

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

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 continued1

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

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

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 continued1

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 continued2

Creating Table Relationships (continued)

  • Relationships window after creating a one-to-many relationship

20

20


Creating a multitable query

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

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

Using Operators in a Condition (continued)

  • Relational operators


Calculating data

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

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

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 continued1

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 continued2

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 continued3

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.


  • Login