Access Queries

1
Access Queries
2
Queries

• Most common type of Query is selection(projection)
  
• Specify sources for data retrieval
• table(s) and/or query(ies)
• Specify attributes(fields) to be retrieved
• Selection criteria
• Calculations that need to be performed
• Action Queries
• Other types such as crosstabs

3
Query Languages

• Relational Algebra
• Set theory
• Provides the conceptual basis for SQL and QBE
• Structured Query Language (SQL)
• The user enters commands according to a
  pre-defined syntax to retrieve desired data.
• Query By Example (QBE)
• The user starts with a sample of the table(s)
  columns and marks the fields he or she wants to
  include in the answer.
• Defaults are available for summarizing and
  manipulating the data.

4
Creating Queries

• Query Wizard
• QBE Query Design
• SQL design (beyond scope of our class)

5
Creating Query 2k7

• Wizards
• Click on create tab
• Select query wizard
• Select simple query
• Select your data source(s)
• Select desired attributes from each data source
• Select next
• Name query
• Click on finish

6
Wizard
Click on create tab and select Query Wizard
7
Click on Simple Query wizard
8
Select data sources and desired attributes from
each source
9
Name your query
10
Resulting Dynaset
11

• Access creates the SQL needed to do the query for
  the wizard
• SQL for previous query example

12
Design View

• Uses QBE (what we will use)
• Design View
• Click on create tab
• Select query design
• Select your data sources and click on add
• Select attributes from each data source and drag
  to grid below
• Run query
• Save query if it will be used on a regular basis

13
Click on create tab and select Query Design
14
Select your data sources and click on add
15
Select your data sources and click on add
16
Select attributes from each data source and drag
to grid below
17
Click on Run symbol to execute qury
18
Resulting Dynaset
19

• Access creates the SQL needed to do the query for
  QBE
• SQL for previous query example

20
2k3

• Wizard and design view for query in 2k3 like 2k7
• Minor differences
• See next 2 slides

21
2k3
22
Run symbol in 2k3
23
Queries with selection criteria

• Use design view when using selection criteria
• Select your data sources and attributes if
  interest from your data sources enter selection
  criteria
• Run query

24
Query to list Customers from Brazil
Selection Criteria
25
Resulting Dynaset
26
Queries(complex selection criteria)

• Comparison operators
• lt,
• gt,
• ,
• lt,
• gt, and
• ltgt Not equal to

• Logical operators
• And,
• Or, and
• Not

27
Multiple Criteria

• AND operator
• Queries that must satisfy more than one
  condition, with all conditions being true, use
  the AND operator
• OR operator
• When any one of several criteria is all that is
  required for a row to be displayed in a dynaset,
  then you use the OR operator

28
And Operator
Selection Criteria
29
Resulting Dynaset
30
OR Operator
Selection Criteria
31
Resulting Dynaset
32
Action queries

• Action Queries
• create tables,
• Delete/remove records,
• update fields, or
• Append/add new records to an existing table
• Action queries alter information as opposed to
  selection queries, which passively display
  information without altering anything
• No undo feature

33
Make Table

• Use design view
• Select source table
• Select attributes
• Select make table query symbol and enter name for
  your table in pop-up box
• Run query
• You will receive a message about the number of
  records being added to table
• Save query if it will be used in the future

34
Select source table Select attributes
35
Select make table query symbol and enter name for
your table in pop-up box
36
(No Transcript)
37
(No Transcript)
38
Make Table Results
39
Delete Query

• Use design view
• Select source table
• Select records/attributes selection criteria
• Select delete query symbol
• Run query
• You will receive a message about the number of
  records being deleted
• Save query if it will be used in the future

40
Select delete query symbol
Criteria for deletion
41
(No Transcript)
42
Update Query

• Use design view
• Select source table
• Select attribute(s) to be updated
• Select update query symbol
• Fill in update to criteria
• Run query
• You will receive a message about the number of
  records being updated
• Save query if it will be used in the future

43
Select attribute(s) to be updated Select update
query symbol
Update to Criteria
44
(No Transcript)
45
Append Query

• Use design view
• Select source table(s)
• Select append query symbol
• Identify destination table that source table will
  be appended to
• Drag fields from source to grid
• Access will automatically map fields from source
  and destination that have same name
• Manual map for all others
• Run query
• You will receive a message about the number of
  records being updated

46
(No Transcript)
47
Select append Query Symbol
48
Select field and drag to grid below Notice that
field mapping has been done for most fields
49
Manual mapping for this field
50
(No Transcript)
51
Results of Append Query
52
Action queries in 2k3

• Works the same as 2k7
• How you make initial selection slightly different
• Go to design view select query from the menu to
  get the action query options
• Select the type of action query

53
Click on query to get the action query options
54
Select the type of action query