Kennel database by g4509244

VIEWS: 24 PAGES: 21

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




                                    1
             Queries
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


                                              2
  When should you use
       queries?

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
                                             3
          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
table(s).
                                               4
        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
edited.
                                       5
        Action queries
* all action queries are not reversible
*




                                          6
       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
employee
* archiving old data
                                      7
        Action queries
Append (qapp)

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

                                       8
         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 <
#01/01/1999#


                                            9
        Action queries
Update (qupd)

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

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




                                       11
          DesignView
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
                 clicked



                                      12
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
                                                                                                                            13
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
    DatePart
                      A specific part of a date   4DatePart ("yyyy",[OrderDate]) =
 (Interval, Date)
                                                  2001




                                                                                                                              14
    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
table
                                                           15
       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.



                                              16
         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.




                                       17
Query Examples
 Parameter Query




                   18
  Query Examples
Calculated Fields with
   concatationing




                         19
     Query Examples
Calculated Fields with math




                              20
Query Examples
  Cross tab




                 21

								
To top