Document Sample
Lecture Powered By Docstoc
					             Lecture 22: Access Queries & Reports

The Query design screen provides a mechanism for selecting
specific data from datafile(s) by:
• displaying only selected fields
  (Can also change the order of fields)
• only records that match given criteria
• reorder records
• can also link across multiple files

  IT Fundamentals                                            1
                   Access Query Screen
Design screen:
contains top section which shows the tables to be used in the
Bottom section:
contains fields and criteria used in the query

 IT Fundamentals                                                2
                      Access Query Screen (cont.)

Selecting Fields:
Highlight the field in the active table.
Drag the field from the active table and drop it in the field view

Selecting Records: CRITERIA
Enter criteria into bottom section of the query design screen.

Character field:
Data is entered into the file skeleton as is or encased in quotes.

Can also retrieve a range of values:
eg.    < "M"

    IT Fundamentals                                                  3

IT Fundamentals              4
                       CRITERIA (CONT.)
Numeric field:

eg. Retrieve all records with pay_rate <= 8.50

     IT Fundamentals                             5
                      CRITERIA (CONT.)
Date Fields:

eg. before 27th July 1987 is entered as   <27/07/87

    IT Fundamentals                                   6
                      CRITERIA (CONT.)
Logical Fields:
Enter Yes or No
Note: NO quote marks (“) – “Yes” (WRONG), Yes (CORRECT)

    IT Fundamentals                                       7
                     MULTIPLE CRITERIA
Condition1 AND Condition2
Enter conditions on the same line.
eg. Department=“Back" AND Pay_Rate > 7.00

   IT Fundamentals                          8
                     MULTIPLE CRITERIA (CONT.)


   IT Fundamentals                               9
                      MULTIPLE CRITERIA (CONT.)
eg2. Pay_Rate between 7.00 AND 9.50

    IT Fundamentals                               10
                     MULTIPLE CRITERIA (CONT.)


   IT Fundamentals                               11
                       MULTIPLE CRITERIA (CONT.)
Condition1 OR Condition2
Enter conditions on different lines in file skeleton:

Eg. Department is either “Back" OR "Centre"

     IT Fundamentals                                    12
                     MULTIPLE CRITERIA (CONT.)


   IT Fundamentals                               13
                      MULTIPLE CRITERIA (CONT.)
Eg. 2 Department is either “Back” OR Pay_rate >9.00

    IT Fundamentals                                   14
                     MULTIPLE CRITERIA (CONT.)


   IT Fundamentals                               15
                       SORTING RECORDS
Access allows you to sort the output in ascending or descending
order on character, numeric & date fields.

     IT Fundamentals                                              16
                     SORTING RECORDS (CONT.)

Example 1: Sort all employees in ascending order according to
Name. Show Name, DOB & Department

   IT Fundamentals                                              17
                     SORTING RECORDS (CONT.)


   IT Fundamentals                             18
                     SORTING RECORDS (CONT.)

Example 2: Sort all employees in ascending order according to
Name within each Department. Show Name, DOB & Department

   IT Fundamentals                                              19
                     SORTING RECORDS (CONT.)


   IT Fundamentals                             20
                      SHOWING RECORDS

Tick the SHOW box if you want the field to be displayed.
Note: Not all SHOW boxes should be ticked.

    IT Fundamentals                                        21
                     SORTING RECORDS (CONT.)


   IT Fundamentals                             22
                       Reports Overview
Reports are an important part of any computer business
Features that are important to obtain meaningful reports:
• presentation must be acceptable, proper page breaks with
  headings on each page.
• must be able to print the report in a variety of orders, eg.
  alphabetical, or listed in order of salary.
• calculate totals of numeric columns (fields).
• group like rows together, and print subtotals for each group.
• eg. subtotal of sales for each customer.

The Access Report Generator does all this.
  IT Fundamentals                                                 23
                    Example of a Simple Report

14/07/07                                              Page: 1
                       Royal Charities Pty. Ltd.
                  Previous Month Collection Amounts

                      NAME            AMOUNT
                       -----------    ------------
                       JOE            60
                       FRED           50
                       JOE            30
                       MARY           100
                       MARY           70
                      JOE             80

IT Fundamentals                                                 24
                    Format of an Access Report
1) Page Header
2) Detail
3) Page Footer

1) Page Header
Usually consists of Page number and Date, title of the report

Consists of:
• Contents of a field of the data file.
  eg. NAME and AMOUNT
• a calculated field
  eg. salary * 0.30
       pay_rate * hours
       qty * item_price
  IT Fundamentals                                               25
                Format of an Access Report (cont.)

3. Page Footer
Can consist of subtotals for the page. Not always necessary in
every report.

Group Bands
Data items can be grouped together by field value.
Heading and subtotals can be printed for each group.

Group Examples:
• Department to give a dept. by dept. listing
• Team in a sporting competition, to provide team by team listing
• Subject in a student database
• Course in a student database

  IT Fundamentals                                                   26
                       Group Bands
Sub Totals in Group Summary Bands
• can be obtained for any numeric field
• Eg.   Sum(weeks_pay)

 Group Header and Group Footer
 Group Header
 Consists of heading for that group eg Department

 Group Footer
 Consists of subtotals for that group eg total pay of
 employees in that department

IT Fundamentals                                         27
                        EXAMPLE 1
The following file is grouped by NAME:
                      NAME AMOUNT
                      --------- ------------
                        JOE               60
                      Subtotal            60
                         FRED             50
                      Subtotal            50
                          JOE             30
                      Subtotal            30
                         MARY             100
                         MARY             70
                      Subtotal            170
                          JOE             80
                      Subtotal            80
                        Total             390
 IT Fundamentals                          ===   28
                      EXAMPLE 1 (CONT.)
However if the file is ordered on NAME:
•all records with the same name are grouped together
•Only one subtotal per name calculated
                         NAME AMOUNT
                         ---------- ------------
                          FRED         50
                         Subtotal      50
                          JOE          60
                          JOE          30
                          JOE          80
                         Subtotal 170
                          MARY 100
                          MARY 70
                         Subtotal 170
    IT Fundamentals
                         Total       390               29
                      SORTING AND GROUPING
In Access the order of data is set in the “Sorting and Grouping”
window, selected from the reports design toolbar.

 Example: Grouping by Department, sorting by Name
 within the department

    IT Fundamentals                                          30
                                 EXAMPLE 2
                             Department : Back

EMPLOYEE                         DATE OF UNION                   PAY WEEKLY
NUMBER NAME                       BIRTH         MEMBER RATE                   PAY
 1013 Bews, Andrew               07/19/64          Y            8.25 330.00
 1026 Hinkley, Ken                09/30/66         Y            6.75 270.00
 1041 Hocking, Steven 01/18/65                     N            6.00 240.00
 1056 Mansfield, Michael 08/08/71                  N            9.00 360.00
     The department footer is SUM(weeks_pay)

If we want to count how many employees are in the Back
Department, the footer will be COUNT(Name)
     IT Fundamentals                                                                   31
                    REPORTS USING QUERIES

Selective Reporting
 Sometimes, we may only want to print certain details from the
 eg.    Print only those who are not union members.

 Selection can be achieved through the use of a Query.
 The data from the query is then used for the report.

  IT Fundamentals                                                32
                               Report Form Layout:
Page Header                               Printed at the top of each page
                    ¦ Date : DD/MM/YY                                     Page : 999 ¦
                    ¦                                                                       ¦
                      ¦                WEEKLY PAYROLL REPORT                              ¦
DEPARTMENT Header                          Text entered here is printed whenever the contents of the
band field value changes.
                    ¦                  Department : XXXXXXXXXX                              ¦
        EMPLOYEE                   DATE                 UNION                       PAY WEEKLY
              NAME                  STARTED              MEMBER                    RATE       PAY
Detail Printed for every record of the file
 XXXXXXXXXXXXXXXXXX              DD/MM/YY                         Y             9999.99 99999.99

DEPARTMENT Footer                          Use to setup subtotals
  IT Fundamentals                                                                                      33
Report Footer              Use to setup grand totals