QUERYING A DATABASE
Download
Report
Transcript QUERYING A DATABASE
QUERYING A DATABASE
By:
Dr.Ennis-Cole
OBJECTIVES:
Learn how to use the Query window in
Design view
Create, run and Save queries
Define a relationship between two tables
Sort data in a query
Filter data in a query
Specify an exact match condition in a query
OBJECTIVES:
Change a datasheet’s appearance
Use a comparison operator to match a
range of values
Use the AND and OR logical operators
Perform the calculations in a query
using calculated fields, aggregate
functions, and record group calculations
QUERIES: An INTRODUCTION
A query is a question you ask about stored
data
Access provides the following capabilities:
–
–
–
–
Display selected fields and records from a table
Sort records
Perform calculations
Generate data for forms, reports and other
queries
– Update data in the tables of a database
– Find and display data from two or more tables
QUERY WINDOW
You use the Query window to create a
Query in Design view
By using QBE, you give Access an
example of the information you are
requesting
Access then retrieves the information
that precisely matches your example
Refer to Figures 3-1 and 3-2
CREATING AND RUNNING A
QUERY
Drag CustomerNum from the Customer
field list
Double click CustomerName
Repeat the steps for the City,
OwnerName and First contact fields
Click the Run button
Save, type a name and close the
window
DEFINING TABLE
RELATIONSHIPS
Define relationships between tables
Use a common field to relate two tables
This process is called a JOIN
When you join tables that have a
common field, you can extract data from
them as if they were a single large table
ONE-TO-MANY
RELATIONSHIPS:
Exists when one record in the first table
matches zero, one or many records in the
second table, or when one record in the
second table matches exactly one record in
the first table
Access refers to the two tables that form a
relationship as the primary table and the
related table
ONE-to-MANY
RELATIONSHIPS:
Primary table: “One” table in a 1:M relation
Related table:“Many” table in a1:M relation
Because a related table is stored in two
tables, inconsistencies between the tables
can occur
There are 3 major anomalies:
– Addition
– Deletion &
– Update anomalies
REFERENTIAL INTEGRITY
Is a set of rules that Access enforces to
maintain consistency between related
tables
Rules of RI:
– When you add a record to a related table, a
matching record must already exist in the
primary table
– If you attempt to change the value of the
primary key in the primary table, Access
prevents this change
– When you delete a record in the primary table,
Access prevents the deletion if matching
records exist in a related table
DEFINING A RELATIONSHIP
When two tables have a common field,
you can define a relationship between
them in the relationships window
In this window, you can view or change
existing relationships, define and
rearrange the table layout
Refer to Figures 3-6 & 3-7
SORTING DATA IN A QUERY
Is the process of rearranging records in a
specified order
Often you need to sort before displaying it
When you sort, you do not change the
sequence of the records in the underlying
tables
Only the records in the query are
rearranged
SORTING DATA IN A QUERY
To sort, you must select the Sort key
Sort keys can be text, number,
date/time, Currency, AutoNumber,
Yes/No
They are not memo, OLE object or
hyperlink objects
You sort keys in either ascending or
descending order
Click the toolbar sort buttons to sort
quickly
SORTING MULTIPLE FIELDS
IN DESIGN VIEW
Sort keys can be unique or non-unique
When the sort key is non-unique,
records with the same sort key value
are grouped together
But, they are not in a specific order
within the group
To arrange these, you can specify a
Secondary Key
SORTING A QUERY
DATASHEET
The first sort key field is called the
primary sort key
A table has at most one primary key,
which must be unique
Any field in a table can serve as a
primary sort key
Access lets you select up to 10 different
sort keys
SORTING & FILTERING DATA
In the Query datasheet, select the field or
adjacent fields on which you want to sort
Click the Sort Ascending button or the Sort
Descending button on the Query Datasheet
toolbar
A Filter is a set of restrictions you place on the
records in an open datasheet or form to
temporarily isolate a subset of the records
In the datasheet, select all for filter
Click the Filter By Selection button the toolbar
DEFINING RECORD
SELECTION CRITERIA FOR
QUERIES
Just as you can display selected fields,
you can display selected records
You must specify a condition as part of
the query
A condition is a criteria that determines
which records are selected
It consists of an operator and a value
It could be a comparison operator or an
exact match
Refer to Figures 3-17 through 3-20
CHANGING A DATASHEET’S
APPEARANCE
You can change characteristics of a datasheet
Click the Maximize button
Click the record selector to select the entire
sheet
Click Format, Font
Scroll, Click 8, Click OK button
Press and hold the shift key, then click the
mouse button, all columns are selected
Double-click the mouse button, all columns
are resized to their best fit
Click Save and Close
DEFINING MULTIPLE
SELECTION CRITERIA FOR
QUERIES
Multiple selection requires you to use logical
operators
They combine two or more conditions
When you want to select a record, only if two
or more conditions are met, use the AND
Operator
When you place conditions in different criteria
rows, you use the OR logical operator
See Figure 3-25
The End !