CSE1IS Information Systems Week 9 Lecture 2 MS Access Reports

1. 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.
• Same report should be able to be run using different data criteria, thus providing information based only on records that satisfy that criteria.
  eg. payroll report on all employees
  and payroll report only on employees whose weekly pay exceeds $300.

Microsoft Access has a report generator (or report writer) and can do all of this.

2. Example of a Report:

The following 3 reports are basically equivalent:

Report#1: Flat report in columnar format is crowded, contains duplication etc.

Date: 14/08/01 Page: 1  THE RACER'S WAREHOUSE WEEKLY PAYROLL REPORT  DEPT LOCATION EMPLOYEE DATE OF PAY WEEKLY NUMBER NAME BIRTH RATE HOURS PAY Top Fuel Calder Park REE1 Reed, Jim 07/19/64 8.25 40.0 330.00 Top Fuel Calder Park DIF1 DiFillipo, Darren 09/30/66 6.75 40.0 270.00 Top Fuel Calder Park REE2 Reed, Steve 01/18/65 6.00 35.0 210.00 Top Fuel Calder Park SPL1 Splatt, Rachel 08/08/71 9.00 10.0 90.00 Top Fuel Calder Park KIR1 Kirby, Robin 10/03/58 7.50 40.0 300.00 Funny Car Willowbank DOB4 Dosbon, Wayne 07/19/64 9.10 30.0 273.00 Funny Car Willowbank RUS3 Russo, Peter 04/23/64 11.00 20.0 220.00 Funny Car Willowbank DEN5 Densham, Gary 04/16/60 8.60 30.0 258.00

Report#2 with suppressed duplication on DEPT and LOCATION columns, plus subtotals:

Date: 14/08/01 Page: 1  THE RACERS WAREHOUSE WEEKLY PAYROLL REPORT  DEPT LOCATION EMPLOYEE DATE OF PAY WEEKLY NUMBER NAME BIRTH RATE HOURS PAY Top Fuel Calder Park REE1 Reed, Jim 07/19/64 8.25 40.0 330.00 DIF1 DiFillipo, Darren 09/30/66 6.75 40.0 270.00 REE2 Reed, Steve 01/18/65 6.00 35.0 210.00 SPL1 Splatt, Rachel 08/08/71 9.00 10.0 90.00 KIR1 Kirby, Robin 10/03/58 7.50 40.0 300.00 ========  1200.00  ========  Funny Car Willowbank DOB4 Dobson, Wayne 07/19/64 9.10 30.0 273.00 RUS3 Russo, Peter 04/23/64 11.00 20.0 220.00 DEN5 Densham, Gary 04/16/60 8.60 30.0 258.00 ========  751.00  ========

Report#3 using report grouping:

Date: 14/08/01 Page: 1  THE RACERS WAREHOUSE WEEKLY PAYROLL REPORT  Department : Top Fuel  Location : Calder Park  EMPLOYEE DATE OF PAY WEEKLY NUMBER NAME BIRTH RATE HOURS PAY REE1 Reed, Jim 07/19/64 8.25 40.0 330.00 DIF1 DiFillipo, Darren 09/30/66 6.75 40.0 270.00 REE2 Reed, Steve 01/18/65 6.00 35.0 210.00 SPL1 Splatt, Rachel 08/08/71 9.00 10.0 90.00 KIR1 Kirby, Robin 10/03/58 7.50 40.0 300.00 ========  1200.00  ========  Department : Funny Car  Location : Willowbank  EMPLOYEE DATE OF PAY WEEKLY NUMBER NAME BIRTH RATE HOURS PAY DOB4 Dobson, Wayne 07/19/64 9.10 30.0 274.00 RUS3 Russo, Peter 04/23/64 11.00 20.0 220.00 DEN5 Densham, Gary 04/16/60 8.60 30.0 258.00 ========  751.00  ========

For the last report:

• there are less columns, but what data fields are required?
• what field(s) is the report grouped by?
• how would the data be sorted?
• where are the column headings placed?
• The last column should be calculated - either by the report writer or the query that collates the data

3. General MS-Access Report Structure:

• the following represents a typical layout for any common report

eg. a simple detail report on the Marina table that contains no grouping :

The design construction screen for this report:

4. Grouping Records

  it is often necessary to display records from the database in a categorised, orderly and sometimes, grouped fashion

for example:  employees by department,
                       students by grade,
                       students by class,
                       children by gender

• Grouping arranges the records in your report into categories
• Can now print subtotals for each category
• You can group your data in a report to present information in a more logical and concise format.
• For example, you may want to group employees by department, then list them in alphabetical order by last name.

The following example shows the billing summary, but grouped by the Marina Type.
The Marina Type column has had its field property set to "Hide Duplicates" :

Design Construction Screen for this report:

• In grouping, reports typically include two additional types of sections: 
       -   a group header and a group footer.
• The group header is printed before the records in a particular group are printed
• Note that in Access the subtotals are stored in a TextBox with the ControlSource set to something like "=sum(columnName)"
• Access will print a subtotal or a grand total depending on where that textBox is located

New format structure for the report:

5. Sorting and Grouping Dialog Box:

• When grouping, the data must be sorted on the columns being grouped (why?)
• Access automatically sorts the report on the columns you select to group on
• To add a Report Grouping section to a report, you may use the report wizard or you can:
  • display the report in Design View,
  • select "Sorting And Grouping" dialog box (from the Toolbar or View Menu --> Sorting and Grouping).
• Select the field or expression on which you would like to sort, and select the order in which you would like the data sorted.
• If you would like to display group summary information, such as total hours, you will need to put it in the group header or footer, and change the Group Header or Group Footer property to Yes.
• Add extra sort fields (column) to sort records within the group

6. Multi-table Databases.

eg. The "Bavant Marine Services.mdb" database (also used in tutorials).

• Reports need to see the data as a single table.
• Use the Query Design to create and save a query that joins the tables required for the report
• Use the saved Query as the RecordSource for the report (In Forms Properties)

The above query collates the data for the following report:

Design construction screen for this report:

References:

• Shelly, Cashman & Rosenblatt, Systems Analysis and Design, 6th Edition,  Course Technology, 2006.

NOTE: this webpage has not been brought up to WCAG 1.0 standard nor does it contain validated HTML