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. IT Fundamentals

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 IT Fundamentals

4. Example of a Simple Report IT Fundamentals

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 IT Fundamentals

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 IT Fundamentals

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 IT Fundamentals

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 === IT Fundamentals

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 IT Fundamentals

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 IT Fundamentals

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) IT Fundamentals

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. IT Fundamentals

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 IT Fundamentals

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 IT Fundamentals

15. LAB 7 • Access Report Generation • p100-103 IT Fundamentals