Database Applications –

Microsoft Access

Lesson 4Working with Queries

36 Slides in Presentation

Lesson 4 – Tutorial 3

For Lesson 4, you will work through Tutorial 3 in your textbook.

The tutorial may expose you to more information than is identified in the course competencies.

This presentation focuses on the skills necessary to achieve the course competencies as outlined in your syllabus.

2

Queries

Retrieve specified information from tables.Generate information to be used in

preparing reports.Perform calculations on data found in

fields of underlying tables.

One of the most valuable tools in Access is the Query. Queries are Access objects used to

3

Difference Between Queries and Filters

Filters are temporaryFilters are placed on data in a single tableQueries are saved as individual objects in

the databaseQueries can select data from multiple

tables

4

Types of Queries

Simple Select Query-retrieves and displays data in specified fields. Select Query with Criteria-retrieves and displays requested fields for

records which meet specified criteria. Crosstab Query-summarizes information by rows and columns. Parameter Query-Displays a dialog box asking the user for criteria for the

query. Delete Query-removes a group of records matching specified criteria. Find Duplicates Query-retrieves records that are entered in a database

multiple times. Find Unmatched Query-finds all records in a table or query that have no

matching records in a related table. Append Query-adds records from an existing table to the end of another

table. Top Values Query-limits the number of responses a query returns. Update Query-changes specified fields fro many records at one time.

The following is a list of various types of queries that can be created in Access:

5

Query OperationsQueries can not only be used to retrieve

data by specifying fields and criteria, but can be used to generate information.

Queries can contain calculated fields that are not found in the underlying tables that the query is based upon.

Queries can contain expressions to perform calculations to generate new information.

Queries can contain Total fields to group by fields and summarize statistical data.

6

Designing Queries

One of the most important skills in query design is critical thinking. Creating select queries can be very simple. Designing queries with multiple criteria requires logical thought and precise definition.

Follow this checklist when creating queries: Thoughtfully define the results you want. List the fields you want returned in the query results. Define and apply the criteria necessary to return the desired

results. Choose the appropriate query type to achieve the desired

results. Run the query to see that the query results match your desired

outcome. Adjust the query design if necessary.

7

Simple Select Queries

A Simple Select Query is used to retrieve only specified fields from an underlying table or tables.

Queries can be created using the Query Wizard or queries can be created in Design View.

To create a simple select query in Design ViewClick Query Design in the Queries Group of the

Create TabAdd the table/s containing the fields you want in the

query resultsDouble-click each field to add it to the query design

grid8

Simple Select Queries – Design Grid

Once you have chosen to Create a query, the Query Design grid displays. You will define your queryby selecting fieldsand placing themon the design grid.

9

Simple Select Queries – Design Grid

Execute the following stepsto complete the query.Double-click the table or tables containing fields desired for the query. (A multitable query is a query based on more than one table.)Double-click each field you wish to display in the query results in the order you want the fields displayed in the query results. 10

Simple Select Query

The following design grid shows three fields out of seven selected from the Employees table.

Remember that simple select queries are used to select and isolate specified fields from a table or tables. As the name implies, these queries are very simple to design.

11

Query Criteria

Criteria are limiting factors placed on data.

Criteria can be added to the query design to limit the records selected for the query results.

Criteria can be specific or you can use wild cards to select records with a certain string of characters.

Criteria can also use logical operators such as AND, OR, and BETWEEN or comparison operators such as >, <, or = to identify requested records.

12

Using Character String Criteria

To place criteria in a query, type the requirement in the Criteria row under the appropriate field. The example below shows that the criteria of “Nashville” must be met in the City field for a record to be selected. Criteria involving text is known as a character string.

Note that text criteria is enclosed in quotes.

13

This is an example of an exact match condition.

Using Logical Operators in Criteria

AND and OR are examples of logical operators that can be used in queries. When the AND operator is used, all criteria must be met. When the OR operator is used, any of the specified criteria may be met.

The example below shows that the criteria of “Nashville” OR Memphis must be met in the City field for a record to be selected.

14

Using Comparison Operators in Criteria

Use comparison operators to compare values and dates. When evaluating dates, a date earlier than the specified date is less than while a date later than the specified date is greater than.

Comparison Operators

> greater than

< less than

>= greater than or equal to

<= less than or equal to

<> not equal to

15

Using Comparison Operators in Criteria – Between OperatorThe Between operator limits data selections within a

specified range. The Between operator must always be paired with the And operator.

When you might use the Between operator:

16

To find data within a date range – Between 1/1/2012 And 12/31/2012

To find data within a certain numerical range, for instance age – Between 18 And 35

To find data within a specified alphabetical range-Between “Abe” And “Ellen”Between “a*” And “l*” (when combined with a wild card)

Comparison Operators

For additional comparison operators along with examples, see page AC 142 of your Access text.

17

Query Results

Query results are displayed in a datasheet. To see the results of your query, click the run button.

18

Sorting Data

Query results can be sorted to provide a more useful arrangement of data.

Data can be sorted in two orders:

ascending – ordered alphabetically from A to Z or numerically from 1 to 100

descending - ordered alphabetically from Z to A or numerically from 100 to 1

19

Sorting Data

Query results can be sorted in datasheet view, however, the sort options in this view are somewhat limited and temporary. For best results, define sort criteria in the Query Design Grid.

Data may be sorted on multiple fields. Each field on which a sort is performed is called a sort field or sort key. The sort field that defines the main order of the sort is the primary sort field. All other sort fields are secondary sort fields. Sort order is determined by the order of the sort fields.

20

Performing Calculations in QueriesQueries can be used to generate data based on calculations performed in the queries. Expressions (formulas) are entered in the query design grid to generate data. Expressions generally include field references, mathematical operators, and constants (stated values).

21

Performing Calculations in Queries – Mathematical OperatorsBelow are the four most frequently used mathematical operators:

Addition +

Subtraction -

Multiplication *

Division /

22

Performing Calculations in Queries – An ExampleYour boss is considering increasing the price of all products by 5%. He has asked that you provided data on what the new product prices will be. You will design a query to calculate the new prices. The field Price contains the current product price. The query will determine the new prices based on the following expression entered in a blank field in the query design grid:

[Price]*1.05In this expression, the field Price is distinguished by placing it in brackets. The mathematical operator for multiplication is entered next, followed by the constant value of 105% (converted to a decimal). Using 105% in the expression will generate the total new price. Using only 5% in the expression would calculate amount of price increase but not the new price.

23

Naming Calculated Fields

Calculated fields in queries will be given the name Expr1 by default. To give a calculated field a more descriptive name, precede the expression with the field name. Differentiate between the field name and the expression with a colon (:). See the example below in which we assign the name for the increased price New Price.

New Price: [Price]*1.05

24

Performing Calculations in Queries – An Example

The query containing the expression would appear as follows in Design View:

The following results are generated from this query:

Notice that the field name given to the calculated field appears as the column heading.

25

26

Take a Break

If you need to take a break, this is a good breaking point. When you return, start with slide #25.

Using Aggregate Functions

Statistical operations can be performed by using the aggregate function options in Access.

Common aggregate function operations includeSum – totals the specified field in a group of records (use with numeric fields only)Count – returns the number of records in a group (use with any field)Average – finds the average value in a group of recordsMaximum – finds the largest value in a group of recordsMinimum – finds the smallest value in a group of records

27

Creating a Statistical Analysis Using an Aggregate Functions

Statistical analyses are often done on one field only. We will look at an example based on the following table. This table has four fields, but management would like an analysis of the Monthly Fee.

28

Creating a Statistical Analysis Using an Aggregate Functions

To create this analysis, first enter the Query Design window.Select only the field you wish to analyze.If you wish to determine more than one statistic on the selected field, add the same field for each statistic you want to use. Turn on Aggregate Functions by clicking the Totals tool:The Totals field will now appear on your query design grid.The default value for the Totals field is Group By. If you click beside Group By, you will get a list of all the aggregate functions.Choose the appropriate function for your analysis.

29

Creating a Statistical Analysis Using an Aggregate Functions

We will view an example assuming management wants to know the highest and average monthly fees.

In the Query Design grid, add the Monthly Fees field twice (add the field twice because you want to perform two analyses on this field—highest and average.)

30

Creating a Statistical Analysis Using an Aggregate Functions

Now turn the Totals field on.

Note the new row in the Query

Design grid.

Recall that the default value for the Total field is Group By.

31

Creating a Statistical Analysis Using an Aggregate Functions

Click beside Group By and a drop-down arrow will appear.

Click this arrow and select the aggregate function necessary to perform your analysis. In this example, we will choose Max (to determine the highest fee) and Avg (to determine the average fee).

32

Creating a Statistical Analysis Using an Aggregate Functions

The results of this query may seem strange since it will be only one row as shown below but these are the correct results.

33

Creating a Statistical Analysis Using an Aggregate Functions Grouped By Multiple Fields

We previously discussed that statistical analyses are usually performed on one field only.

Now let’s assume that management wants to see the highest and average fees organized by city served.

The only change to the query to accomplish this is to add the City field (from another table)and leave this field at the default Group By setting as shown.

34

Creating a Statistical Analysis Using Aggregate Functions with aGrouped By Field

The results of the query with a Group By field added are shown below:

Management can now see the highest fee and the average fee for each city served by this company.

35

End of Lesson

Work through Tutorial 3 on pages AC 116 – 170 in your Access textbook. (Recall that the tutorial may expose you to more information than is identified in the course competencies).

Complete the assigned exercises. (Exercise objectives will be limited to course competencies.)

36