Kennel database by g4509244


									       Kennel database
1.   Add 5 mom dogs
2.   Add 5 litters
3.   Add 3 puppies to each litter

Create a "recordset“

- transient pseudo-table
- exists only for the duration of the query
- refreshed from the table(s) each time
the query is run

  When should you use

Select   Queries
  Quick  list of data
  Quick list of data in a sorted sequence

  List of data that answers a question

  Summarize data

  Supply the results of calculations

  Cross-tabulate data

Action   Queries
  Add  data
  Edit data

  Delete data
          Select queries
Simple (qry)

- selection of a set or subset of the fields
from one or more tables
- criteria [CustCity]="Toronto"
       [ItemCost] > 500
- complex AND/OR relationships
- sorting
- filter for displaying/updating a subset of
        Select queries
Crosstab (qxtb)

- like the kilometes table on a map
- total sales by month for each
salesperson in a company
- displays information down the left
column and also across the top
- data in Crosstab queries cannot be
        Action queries
* all action queries are not reversible

       Action queries
Make Table (qmak)

- make a new table based upon one
(or more) other tables
- selection criteria
- usage * increase response time by
creating a 3rd table
* send specific data to another
* archiving old data
        Action queries
Append (qapp)

- append to the end of an existing
- only fields which have the same
names are retained in the new tables
- usage * archiving a history of all
sales for the month

         Action queries
Delete (qdel)

- deletes rows from a table, based upon
specific criteria
- used in conjunction with a Make Table /
Append for removing records that have
been archived from the main table(s)
- usage * remove all records with date <

        Action queries
Update (qupd)

- modify or correct information in the
- usage * change the name of a city
from "Berlin" to Kitchener"
* increase fee of all part timers by

     Parameter Queries
- increased flexibility
- displays a dialogue box to prompt
the user for additional information.
[Enter Customer to be selected:]

Selection of fields in query designview
 Select field, then drag and drop onto
             the query grid
  Holding SHIFT and clicking on one
   field and then another will select
  those fields and all between them
  Holding CTRL will select each field

Operator                    Meaning                              Example                                    Result
                                                                                         Finds only those records with “Sales” as the
       =                        Equal to                          =”Sales”
                                                                                               field value
                                                                                         Finds all records with values less than 100 in
       <                     Less than                              <100
                                                                                               that field
                                                                                         Finds all records with values less than or
       <=              Less than or equal to                       <=100
                                                                                               equal to 100 in that field

       >         Greater than                                       >100

       >=            Greater than or equal to

                                                                                         Finds all records with values other than Sales
       <>                    Not equal                           <> “Sales”
                                                                                                               in the field
                                                   Created by adding criteria on the
      AND          Both conditions must be true         same line of the query grid to
                                                        more than one field
                                                                                         Finds all records with the value “NF”, “NB”, or
      OR            Either condition can be true             “NF” or “NB” or “PE”
                                                                                               “PE” in the field
                 Compares a string expression to                                         Finds all records with the value of “New” at
      Like                                                      Like “ New* ”
                              a pattern                                                        the beginning of the field
                                                                                         Finds all records with the values of 5 through
    Between           Finds a range of values                 Between 5 and 10
                                                                                               10 (inclusive) in the field

       In                  Same as OR              IN (“NF”,”NB”,”PE”)

      Not               Same as not equal          Not “New”

     Is Null                Finds Null             Is Null

   Is Not Null       Finds all records not null                   Is not null
Sample Date Criteria

     Date ( )              Current Date                         Date ( )

                                                                                     Records with the order on the first day of
                                                                                     the month
   Day (Date)            The day of a date               Day ( [OrderDate] ) = 1

                                                                                     Records with the order in April
  Month (Date)         The month of a date              Month ( [OrderDate] ) = 4

                                                                                     Records with the order in 2001
   Year (Date)          The year of a date             Year ( [OrderDate] ) = 2001

                                                                                          Records with the order on Sunday
 Weekday (Date)     The weekday of a date             Weekday ( [OrderDate] ) = 1

                                                                                                 All records in 2001
Between Date and                                        Between #1/1/2001# and
                         A range of dates
     Date                                                    #12/31/2001#

                                                                                           All records in the forth quarter
                                                  DatePart ("q",[OrderDate]) =                    All records in 2001
                      A specific part of a date   4DatePart ("yyyy",[OrderDate]) =
 (Interval, Date)

    Pitfall of Multitable Queries

Fields that cannot be updated in an update query ---
these are the join fields on the “one” side of a one-to-
many relationship (unless the Cascade Update referential
integrity feature has been activated).

Which table to use in a query can cause confusion?

 If wanting all details from a customer, probably the
subform should be used

 If wanting a customer list, probably just the customer
       Calculated Fields
One of the rules of data normalization is
that results of calculations shouldn’t be
included in your database.
You can output the results of calculations
by building those calculations into your
queries, and you can display the results of
the calculations on forms and reports by
making the query the foundation for a form
or report.

         Total Queries
Select Totals under the View Pull-
Down menu. It’s important that you
add the fields in the order in which
you want them grouped.

Query Examples
 Parameter Query

  Query Examples
Calculated Fields with

     Query Examples
Calculated Fields with math

Query Examples
  Cross tab


To top