Queries by liwenting


									                About Queries
• Query represents a question and an answer
   – Developed using design grid in Query window
   – Answer is displayed in Results table that contains
     records that satisfy criteria specified in query
• Use queries to select records (Select Query)
• Use queries to Update records (Action Queries)
• Use results of queries to generate reports and
               Select Query
• A query that searches the underlying table
  to retrieve the data that satisfies the query
• View some or all of the fields for selected
• sort the data.
• select specific records from a table (or
  several tables) that meet certain criteria
• perform calculations to display data not
  stored in the underlying tables
           Creating Queries
• Use Simple Query Wizard
  – Make very basic queries based on one table
• Design your own with Query By Example
  (QBE) grid
                  Results Table
• Result of query stored in a Results Table
   – Results Table contains records that satisfy criteria
     specified in query grid
   – Temporary Table – deleted when Results table is closed
• Acts like a table
   – Enables you to enter new record
   – modify or delete existing records
   – Any changes made in the Results Table are
     automatically reflected in the underlying table
              Query Window
• Design View – used to create a query
  – Field List- contains the list of fields from the
    tables from which the query will be based
  – Query Grid- contains specifications for the
• Datasheet View –displays the results of
  running the query (Results Table)
               Query Grid
• Field Row- displays field names displayed
  in query results
• Sort Row- enables you to sort fields in
  ascending or descending order
• Show Row – controls whether or not the
  field is displayed in the Results Table
• Criteria Row – determines the records to be
               Query Grid
• Moving a field to a different location
• Saving and Naming the Query
     Defining Record Selection
• Specify a condition as part of the query in the
  Criteria row of the Design Grid
• A condition is a criterion or rule that determines
  which records are selected
• Conditions usually consists of a comparison
  operator and a value
   – e.g. =“Grand Rapids” or >12
• A Comparison operator asks Access to compare
  values of a database field to the condition for each
  record in the query and display the records for
  which the condition is true
       Comparison Operators
Operator    Meaning           Example
=           Equal to          “Acc”
<           Less than         <100
<=          Less than or      <=100
            equal to
>           Greater than      >100
>=          Greater than or   >=100
            equal to
<>          Not equal to      < >”Acc”
           Special Operators
Operator      Meaning            Example

Like          Compares string    Like “SM*”
              expression to      Like “H?ll”
Between       Finds a range of   Between 21 And
              values             65
Is Null       Finds null value   Is Null
               Entering Criteria
• Text fields enclosed in Quotes
• Number or Currency entered as digits with or
  without decimal point. No commas or $ allowed
• Dates enclosed in pound sign (#)
• Use Yes (true) or No (false) for Yes/No
• Wild Cards allow you to search for pattern within
  a Text field
   – * (asterisk) stands for any number of characters
   – ? Stands for a single character in the same position as
     question mark
    Multiple Selection Criteria
• Use Logical operators to combine two or
  more conditions
  – List all faculty hired after 1990 who are Full
  – List all students who are either Finance,
    Accounting, or Marketing majors
           Logical Operators
Operator      Meaning          Example

And           All conditions    Add criteria on
              must be true      same row
                                “CA”       >100
OR            At least one      “CA” or “NY”
              condition is true or “AZ”
NOT           Negates logical Not (“AZ” or
                expression    “CA”)
       AND Logical Operator
• Record selected only if all conditions are
• Place criteria in separate fields of same
  Criteria row
• Use AND operator if multiple conditions
  apply to same field
  –   >= #1/1/02# AND <= #1/31/02#
           OR Logical Operator
•   Record selected if at least one condition is true
•   No records selected if all conditions are false
•   Place conditions in different Criteria rows
•   Use OR operator
    – “CA” OR “AZ”
• Use IN operator
    – IN(“CA”,”AZ”,”WA”)
     Creating a Calculated Field
• Query can perform calculations. A calculated field
  is a field that displays the results of an expression
• Define an expression containing a combination of
  database fields, constants, and operators
• Add calculated field to query grid. Result appears
  in the Results table but is not stored in the
  database table
             Calculated Fields
• To create an expression to calculate the 6% sales
  tax on an order
   – e.g. SalesTax:InvoiceAmt * .06
• This calculation, or expression, is evaluated for
  each record
• The newly created “field” in the query is called
  SalesTax and can be referenced, but not edited on
  future forms and reports
• You can concatenate two text fields together using
  the & (concatenation) operator
   – e.g. FullName:LastName & “, “ & FirstName will
     display “Smith, Jim” as a calculated field
           Calculated Fields
• To create a calculated field you can simply
  type the expression/calculation into the field
  row of a query
• Use the Expressions builder to help you
  create the expression
• You may also reference many of the built-in
  functions Access provides in the field row
           Arithmetic Operators
Operator             Meaning
+                    addition
-                    subtraction
/                    Division
*                    Multiplication
^                    Exponentiation
          Expression Builder
• Tool to help build expression in query
• Contains
  – Expression box to enter expression
  – Buttons for common operators
  – One or more lists of expression elements such
    as tables and field names
                Multiple Tables
• You can create queries with multiple tables
• Tables typically are related
• Explicitly related tables will automatically follow the
  definition of the relationship when they are brought into
  the query
• You can select any/all/none of the fields from each table to
  display on the query
• You can created calculated fields which use any field on
  any table in the query
• To add tables to a query select the Show Tables button or
  select Query|Show Table from the menu
       Multiple-table Queries
• Joining tables – returns only those records
  from both tables that have a matching
  – Normally tables that are related can be joined
               Totals Query
• Obtain summary information from a group
  of records using aggregate (summary)
  – Example: What are total sales for all of January
     Totals / Aggregate Queries
• You can summarize numeric data
   – Sum, Average, Count, Minimum, and Maximum
• Calculations can be performed on all of the
  records in your query or groups of data
   – e.g. subtotals
   – Note that subtotals and totals for all records can not be
     calculated on the same query
   – To show the Total or Aggregate functions on a query
     select the Totals button or select View|Totals from the
     query menu
                Total Query
• Total button  on the toolbar gives you
  access to aggregate functions
• Group By operator– entry that indicates the
  records are to be divided into groups based
  on like values
  – One row in the results table for each unique
    value in the group by field
  – Ex: Average salaries by department
                     Group By
• The Group By aggregate function combined with
  the Totals aggregate functions is very powerful
   – GroupBy as many fields as you want and Access will
     automatically calculate the total function for each group
• Selecting Group By will create a new row in your
  query for each combination of Group By fields
   – Group By City will give a new row for each city
   – Group By City with Group By Date will give a new
     row for each combination of City and Date that exists
     in the table

To top