Microsoft Access

1
Microsoft Access

• Querying a Database Using the Select Query Window

2
Objectives

• Creating and running queries
• Using compound criteria in queries
• Sorting data in queries
• Using a saved query
• Joining tables in queries
• Performing calculations in queries
• Using a grouping in queries

3
Types of Queries

• Queries can be used to view, and analyze
  information in a database
• In addition, update queries can be used to change
  data
• Types of queries
• Select queries
• Parameter queries
• Update queries
• Crosstab queries
• Saved queries can be used to generate reports

4
Creating Select Queries

• Most common of all queries. Data can be retrieved
  from one or more tables results are displayed in
  datasheet view
• Select a basis table for the query
• Click on the New Object button arrow, and select
  Query
• Select Design View and click OK
• Double click on basis table fields to insert them
  into the query
• Run the query

5
Select Query Datasheet View
6
Creating Parameter Queries

• Parameter queries prompt a user for search
  criteria with its own dialog box
• Start with a Select Query, and save as Client
  Parameter Query
• Add bracketed text in a criteria field to prompt
  the user for parameter value

7
Parameter Query Datasheet View
8
Creating Update Queries

• Update queries are used to make global data
  changes in one or more tables
• Start with a select query, save as Client Update
  Query
• In the Query menu, select Update Query
• Enter the new value in the Update To field
• Enter the old value to search for in the Criteria
  field
• Run the query, confirm changes

9
Review Changed Database

• All occurrences of 53 in the Trainer Number field
  have been updated to 42 in the Client table

10
Creating Crosstab Queries

• Crosstab queries are used to display aggregate
  data (such as count, total, average) in matrix
  format
• Start with a Select Query, save as Client
  Crosstab Query
• In the Query menu, select Crosstab Query
• Select Clear Grid in the Edit menu
• Double click the City, Trainer Number, and Amount
  Paid fields from the Client table
• Add a field named Total Paid Amount Paid
• Refer to the picture for the Total and Crosstab
  settings
• Run the query, confirm aggregate data is
  correctly displayed

11
Crosstab Query Datasheet View
12
Using a Saved Query for Reports

• A saved query can be used for generating reports
• Select a saved query listed in Queries Objects
• Click on the New Object button arrow and select
  Report
• Select Report Wizard and click OK
• Pick fields you wish to see on the report
• Can add grouping level(s)
• Can sort records by up to four fields
• Specify layout and style, and save the report
• Click Finish to generate report

13
Using Compound Criteria in Queries

• Queries can contain compound search criteria
• AND clauses are specified by entering criteria on
  the same row across different fields
• OR clauses are specified by entering criteria on
  rows below other fields
• Can combine AND with OR clauses
• Can use wildcards to specify groups of items
• Specifies clients whose names start with an F AND
  has paid less than 20000, OR any Client Number
  that starts with a CE

14
Compound Criteria Datasheet View
15
Sorting Data in Queries

• Any field in a query can be sorted in ascending
  or descending order
• In Design View, look for the field to sort by
  (i.e. Amount Paid)
• Click on the Sort pull-down menu, and select
  Ascending or Descending
• Run the Query, and confirm data was sorted
  properly

16
Data Sorted by Amount Paid
17
Joining Tables in Queries

• Data needed for queries often reside on different
  tables. A join allows queries to retrieve
  relevant records from related tables
• Use the Trainer table as basis for a new query,
  and add the Client table into the query. Trainer
  Number joins the two tables in the query

18
Trainer and Client Tables Joined by Trainer Number
19
Performing Calculations in Queries

• Create a new field to display calculations based
  on the values of fields in a database
• Within Design View
• Type an expression in an empty cell in the Field
  row
• Field names must be bracketed (i.e. Amount
  Paid)
• The following calculates the Taxes Paid by
  multiplying the Amount Paid by the CA sales tax
  rate