Two Ways of Sorting Data

 Sorting
Sorts ascending or descending on any field
Queries


 Sorts and displays all the records in a

Getting Information out of a Database single table
 Filtering
 Displays certain records in a single table

 Both used dynamically – not saved with

the table

Query Steps for Querying

 = a question represented CREATE the query
in a way that ACCESS can  Select the table(s) involved in the query
understand  select fields involved in the query
 Can be used to sort, select  select records to display - establish criteria
and view records from
more than one table RUN the query
 see the resulting dynaset = DYNAmic SET
of records to match selection criteria
SAVE the query if it is a question you ask
often

1
 Query Window Query Windows (cont.)
 Field: name of an attribute involved
in the query
 Table: table where the field is found
 Sort : the query will be sorted
according to this field
= Table(s) from which
 Sort Key: Attributes on which the
to choose fields query will be sorted
 Compound Sort Key

 Sort Key at the left is the

AND Primary Sort Key

 Show:
 On | Off: The field appears or not

OR in the result of the query

 Criteria: Condition on a field that a
record must satisfy to be selected by
= QBE (Query By Example) - place to the query.
specify fields, criteria, sorting, etc.  AND: Criteria on the same line

 OR : Criteria on different lines

Example Query How does it work ?

Displays Last Name, First Name and age of female
employees sorted by Last Name, then First Name
 How does a query work to retrieve the
correct information ?
 A step by step look at the first query
Sort by L. Name , F.Name
SSN LName FName Address City Zip Sex Age Salary DepID ProjectID

523-41-0727 Lee Tony 5. Blue Ct. Sausalito 94252 M 23 67000 1 X

No Show
711-73-2486 Yep Lisa 42A Greer Fremont 95110 F 25 73000 2 Z
141-71-5901 Sorensen Scott 103 Pleasant San Mateo 94335 M 26 85000 1 X
293-21-7720 Vazquez Raul 4320 Mission Daily City 94017 M 21 75000 3 S3
566-02-5572 Drobnis Gloria 2 Glenellen Daily City 94012 F 27 76200 1 S3

222-02-2220 Lee Bruce 23 Port Rd. Beverly Hills 94016 M 28 75000 3 S3

 Condition Marie San MAteo 94335 F 25 50000 1
111-01-111 Sorensen 103 Pleasant
Criteria
Sex =“F”

2
SSN LName FName Address City Zip Sex Age Salary DepID ProjectID Another Simple Query
Using a Range Criterion
523-41-0727 Lee Tony 5. Blue Ct. Sausalito 94252 M 23 67000 1 X

711-73-2486 Yep Lisa 42A Greer Fremont 95110 F 25 73000 2 Z

141-71-5901 Sorensen Scott 103 Pleasant San Mateo 94335 M 26 85000 1 X
293-21-7720
566-02-5572
Vazquez
Drobnis
Raul
Gloria
4320 Mission
2 Glenellen
Daily City
Daily City
94017
94012
M
F
21
27
75000
76200
3
1
S3
S3
Display SSN, First Name, Last Name and age of
222-02-222 Lee Bruce 23 Port Rd. Beverly Hills 94016 M 28 75000 3 S3 employees whose ages range between 20 and 24
111-01-111 Sorensen Marie 103 Pleasant San MAteo 94335 F 25 50000 1

Apply Selection Criteria: Sex = “F” Specify Ranges using

SSN LName FName Address City Zip Sex Age Salary DepID ProjectID Between, >, >= , < , <=
Condition Criteria
711-73-2486 Yep Lisa 42A Greer Fremont 95110 F 25 73000 2 Z
566-02-5572 Drobnis Gloria 2 Glenellen Daily City 94012 F 27 76200 1 S3

111-01-111 Sorensen Marie 103 Pleasant San MAteo 94335 F 25 50000 1

Specifies ages between 20 and 24

Remove fields: Sort the records

• Not in the query LName FName Age LName FName Age

• In the query : Show Off Yep Lisa 25 Drobnis Gloria 27

Drobnis Gloria 27 Sorensen Marie 25

Sorensen Marie 25 Yep Lisa 25

DYNASET

Simple Query: Like Operator Query : Or on One Field

Display SSN, Last Name and First Name of employees  Display SSN, First Name, Last Name and City of
whose Last Name is like “S*s?n” employees who lives in Sausalito or Palo Alto
LIKE searches for a particular
pattern in a field
* : (Wildcard) Any number of
characters (0 or more)
? : Wildcard: Any single
character
Examples:
“S*”
“*th*
“*en” City = “Sausalito”
“S*s?n” Or

Condition Criteria City = “Palo Alto”

Last Name like “S*s?n”

3
 Query : Or Condition Query: And between several
between two fields fields
 Display employees who lives in Palo Alto or are males  Display employees who are males and live in Sausalito

City = “Sausalito”
City = “Palo Alto”

AND
OR
Sex = “M”
Sex = “M”

Query: And between several And-OR Criteria among several

fields fields
Display employees who are males and live in Sausalito or Display female employees that live in Daily City or Palo Alto
Palo Alto

(City = “Palo Alto”

Or
City = “Sausalito”)
City = “Palo Alto” AND
AND Sex=“F”
Sex = “M” OR
City = “Daily City” AND
Sex=“F”

4
 Condition for fields Condition for fields
that contains any value that does not contain any value
 Display SSN, Last Name and First Name of  Display SSN, Last Name and First Name of
employees who are assigned to any project employees who are not assigned to any project

TRUE The field contains a value (any value) TRUE The field does not contains a value ( Null Value)
Is Not Null Is Null
FALSE The field does not contains a value ( Null Value) FALSE The field contains a value (any value)

A More Complex Query

Two Related Tables How does it work ?
SSN LName FName Address City Zip Sex Age Salary DepID ProjectID

Display the SSN, Last Name, First Name and the 523-41-0727 Lee Tony 5. Blue Ct. Sausalito 94252 M 23 67000 1 X

department name of female employees 711-73-2486

141-71-5901
Yep

Sorensen
Lisa

Scott
42A Greer

103 Pleasant
Fremont

San Mateo
95110

94335
F

M
25

26
73000

85000
2

1
Z

293-21-7720 Vazquez Raul 4320 Mission Daily City 94017 M 21 75000 3 S3

566-02-5572 Drobnis Gloria 2 Glenellen Daily City 94012 F 27 76200 1 S3

222-02-222 Lee Bruce 23 Port Rd. Beverly Hills 94016 M 28 75000 3 S3

111-01-111 Sorensen Marie 103 Pleasant San MAteo 94335 F 25 50000 1

+
Access Joins the Tables by matching Dept.ID Dept.Name.

rows with the same value in their 1 Accounting

common attributes (DeptID)

2 Payroll
3 Sales

This creates a Virtual Table

SSN LName FName Address City Zip Sex Age Salary DepID ProjectID Dep. Name

523-41-0727 Lee Tony 5. Blue Ct. Sausalito 94252 M 23 67000 1 X Accounting

711-73-2486 Yep Lisa 42A Greer Fremont 95110 F 25 73000 2 Z Payroll

141-71-5901 Sorensen Scott 103 Pleasant San Mateo 94335 M 26 85000 1 X Accounting

293-21-7720 Vazquez Raul 4320 Mission Daily City 94017 M 21 75000 3 S3 Sales

566-02-5572 Drobnis Gloria 2 Glenellen Daily City 94012 F 27 76200 1 S3 Accounting

222-02-222 Lee Bruce 23 Port Rd. Beverly Hills 94016 M 28 75000 3 S3 Sales

How does it work ? 111-01-111 Sorensen Marie 103 Pleasant San MAteo 94335 F 25 50000 1 Accounting

5
 How does it work ? Calculated Fields
Apply Criteria : Sex = “F”
SSN

711-73-2486
LName

Yep
FName

Lisa
Address

42A Greer
City

Fremont
Zip

95110
Sex

F
Age

25
Salary

73000
DepID

2
ProjectID

Z
Dep. Name

Payroll
 Used to compute values in a query:
566-02-5572

111-01-111
Drobnis

Sorensen
Gloria

Marie
2 Glenellen

103 Pleasant
Daily City

San MAteo
94012

94335
F

F
27

25
76200

50000
1

1
S3 Accounting

Accounting
 Syntax: calculatedFieldName:expression
 Any field name included in the expression
Remove fields: must be between brackets
LName FName Dep. Name

• Not in the query Yep Lisa Payroll

• In the query : Show = Off

Drobnis Gloria Accounting

Sorensen Marie Accounting

 Examples:
Sort Records (if required) ssnYearlyPayment: 0.06*[Salary]
LName FName Dep. Name

Yep

Drobnis
Lisa

Gloria
Payroll

Accounting
FirstInitial:Left([First Name],1)
Sorensen Marie Accounting

DYNASET

Calculated Fields Calculating Statistics

Display SSN, first initial, last name, yearly payment to SS  Built-in statistics:
 MAX
 MIN
 SUM
Name of the calculated field  AVERAGE, etc.
Expression
• Group by: creates groups of records that
share common characteristics and
compute the statistics over each group
 Where: establish a criteria for selecting
some of these groups

6
 Display total number of employees
Some queries using statistics of the company

Questions such as:

 How many employees are currently working
in the company ?
 What is the average salary of the employees
in each department ?
 What is the average salary of the employees
in the company
 What is the maximum number of employees
assigned to a project ? This is a tricky one

Display for each department, department Display the average salary of the employees
name and its number of employees that works for the Accounting Department

7
 Display for each department, its name and Display the maximum number of
the average salary of its employees employees assigned to a project
1. Create a query to compute the number of
employees per project

Display the maximum number of Display for each project, its name and its
employees assigned to a project (cont.) number of employees assigned to it
2. Create a query that uses the previous created 1. Create a query to compute the number of employees
query to compute the maximum number of per project (We can use the previous one)
employees in a project

8
 Display for each project, its name and its
number of employees assigned to it (cont.) Parameter Queries
2. Create a new query that include the table
project and the previous query A parameter query is a query that when executes
displays its own dialog box prompting the user
for information.

For example:
Access automatically
sets a relationship for
this query between Display SSN, First Name and Last name of all
the project table and
the appropriated field employees who lives in a city provided by the
in previous query user

Display SSN, First Name and Last name of all Display SSN, First Name and Last name of all
employees who lives in a city provided by the user employees whose salary is in a salary range
provided by the user

Enter the user prompt between [ ]