Return to IFS 105 Notes!

Access
Project 2

Querying a Database
Using the Select Query Window

Querying a Database

• Query - simply a question represented in a way Access can understand
• Run the Query - perform steps necessary to obtain the answer

Select Query Window
(2 parts)

1. Top portion - contains fields of tables which can be used in query
2. Bottom portion - contains field, show, criteria, sort, and total information that can be used to set-up the query parameters

Running a Query

• Run Query - click Run button (!)
• Dynaset - set of records that make up the answer to query request (dynamic set of records)

Note: dynamic because if table changes on which the query depends the results of query will change along with it.

Note: Run button changes to Print Preview

2 Views of Query

1. Design View - used to set-up query
2. Print Preview View - shows dynaset of query

General Terms

• Design View button - click to change from displaying the dynaset to Select Query window
• Clear Grid command on Edit menu - clears QBE Grid (bottom part of Select Query window)
• Criteria row - used to enter criteria for fields in QBE Grid
• Show check box - in Show row of QBE grid, used to hide fields in dynaset that are used as criteria

Wildcards
2 Special kinds

1. Asterisk (*) - collection of characters
2. Question mark (?) - individual character

Example: criteria row Fa* for Name field

Creating a Parameter Query

• parameter query - query that prompts for input whenever it is run.
  • enter a parameter at run-time, rather than a specific value in the criteria row of the QBE grid
  • created by enclosing a value (prompt) in Criteria row in square brackets
    • important that value in brackets is not an existing field name

Example: [Enter City] for City field in Criteria row

Note: parameter queries useful in cases where query run frequently with slight changes to criteria.

Using Compound Criteria
(2 types)

1. AND criteria - each individual criterion must be true in order for the compound criterion to be true.
       To combine criteria with AND, place criteria on same line.
2. OR criteria - either or both criterion must be true in order for the compound criterion to be true.
       To combine criteria with OR, criteria must be entered on separate line (using OR row on QBE grid)

Sorting Data in Query

• Major key - primary key most important (major sort key follows word within in sort phrase)
• Minor key - secondary key less important

Note: can’t sort on an asterisk (used to add all fields of table to QBE grid)

Note: major key must appear to left of minor key in QBE grid (if another order is desired in dynaset, must include major key twice in QBE grid and hide leftmost major key)

Creating a Top-Values Query

• top-values query - query that allows you to quantify the results.
  • show only specified number of records or a percentage of records.
  • sort records, limit results having highest or lowest values
    • uses Top Values box on Query Design toolbar

Example find top 25%: click Top Values Box and select 25% after sorting the data in descending order.

Joining Tables

• Join tables - find records in two tables having identical values in matching fields

Note: One of the key features that distinguishes DBMS from file systems is the ability to join tables. (create queries that draw on data from two or more tables)

Note: Several types of joins are available, the kind used here in called a natural join the most common type.

Calculated Fields in Query

• Calculated field - combination of fields
• Format: Name of Calculated Field, colon, then expression of fields in Field row of QBE grid

Example: Net Pay:[Gross Pay] - [Deductions]

• Zoom dialog box (SHIFT+ F2) - computed field (Right-click field , click Zoom in shortcut menu

Note: Field Names in expression must be enclosed in brackets

Calculating Statistics

• Built-in-functions - COUNT, SUM, AVG, MAX, MIN, STD, VAR, FIRST, LAST

Note: to use any of these functions include them in Total Row of QBE grid

• Total Row - does not routinely appear in QBE grid (select View menu, choose Totals command) (Right-click QBE grid, click Totals) (Click totals button on toolbar)
• Where entry in Total Row used to enter criterion in field that will use total row (Access evaluates Where criteria before performing calculation)

Grouping

• Grouping - groups of records that share common characteristic
• Indicate grouping in Access - select Group By as the entry in Total row for field to be used for grouping

Crosstab Queries

• crosstab query - calculates a statistic (sum, average, count) for data grouped by two different types of information.
  • one of the types will appear down the side of the resulting datasheet, and the other will appear across the top.
  • useful for summarizing data

In the Lab 2
(HOMEWORK #8)

• Page AC 110&111

End

Querying a Database
Using the Select Query Window

Computer Assignment 8

Return to IFS 105 Notes!