ACCESS REPORT GENERATOR

1
ACCESS REPORT GENERATOR
2
Reports Overview
Reports are an important part of any computer
business application. 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.

3
Example of a Simple Report
14/07/2007 Page
1 Royal Charities Pty. Ltd.
Previous Month Collection Amounts
NAME AMOUNT -----------
------------ JOE 60 FRED 50
JOE 30 MARY 100 MARY 70
JOE 80 390

4
Example of a Simple Report
5
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
• 2) DETAIL
• 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

6
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

7
Group Bands

• Sub Totals in Group Summary Bands
• can be obtained for any numeric field
• Eg. Sum(weeks_pay)
• Count(Name)
• Average(hours_worked)

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
8
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

9
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 Total
390
10
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
11
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


1200.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)
12
REPORTS USING QUERIES
Selective Reporting
Sometimes, we may only want to print certain
details from the table. 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.
13
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

99999.99


Report Footer Use to setup grand totals
14
EMPLOYEE Report Form Layout

• The Access Report Form is divided up into many
  groups.
• The format of the employee departmental list we
  are going to construct is 
• Page Header Printed at the top of each page
• DEPARTMENT Header Text entered here is printed
  whenever the contents of the band field value
  changes.
• DETAIL  Printed for every record of the file  
• DEPARTMENT Footer Use to setup subtotals
• PAGE Footer  Use to setup grand totals

15
LAB 7

• Access Report Generation
• p100-103