Kennel database

1
Kennel database

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

2
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

3
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

4
Select queries

• Simple (qry)
• - selection of a set or subset of the fields from
  one or more tables
• - criteria CustCity"Toronto" ItemCost gt
  500
• - complex AND/OR relationships
• - sorting
• - filter for displaying/updating a subset of
  table(s).

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

6
Action queries

• all action queries are not reversible

7
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

8
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

9
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 lt
  01/01/1999

10
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

11
Parameter Queries

• - increased flexibility
• - displays a dialogue box to prompt the user for
  additional information.
• Enter Customer to be selected

12
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

13
Refining your query with Criteria
Operator Meaning Example Result
Equal to Sales Finds only those records with Sales as the field value
lt Less than lt100 Finds all records with values less than 100 in that field
lt Less than or equal to lt100 Finds all records with values less than or equal to 100 in that field
gt Greater than gt100
gt Greater than or equal to
ltgt Not equal ltgt Sales Finds all records with values other than Sales in the field
AND Both conditions must be true Created by adding criteria on the same line of the query grid to more than one field
OR Either condition can be true NF or NB or PE Finds all records with the value NF, NB, or PE in the field
Like Compares a string expression to a pattern Like New Finds all records with the value of New at the beginning of the field
Between Finds a range of values Between 5 and 10 Finds all records with the values of 5 through 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
14
Refining your query with Criteria
Sample Date Criteria Sample Date Criteria Sample Date Criteria Sample Date Criteria
Date ( ) Current Date Date ( )
Day (Date) The day of a date Day ( OrderDate ) 1 Records with the order on the first day of the month
Month (Date) The month of a date Month ( OrderDate ) 4 Records with the order in April
Year (Date) The year of a date Year ( OrderDate ) 2001 Records with the order in 2001
Weekday (Date) The weekday of a date Weekday ( OrderDate ) 1 Records with the order on Sunday
Between Date and Date A range of dates Between 1/1/2001 and 12/31/2001 All records in 2001
DatePart (Interval, Date) A specific part of a date DatePart ("q",OrderDate) 4DatePart ("yyyy",OrderDate) 2001 All records in the forth quarter All records in 2001
15
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

16
Calculated Fields

• One of the rules of data normalization is that
  results of calculations shouldnt 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.

17
Total Queries

• Select Totals under the View Pull-Down menu. Its
  important that you add the fields in the order in
  which you want them grouped.

18
Query ExamplesParameter Query
19
Query ExamplesCalculated Fields with
concatationing
20
Query ExamplesCalculated Fields with math
21
Query ExamplesCross tab