Return to IFS 110 Notes!

Tutorial 5
Creating Complex Reports

 Introduction:  Creating a Report Master

n     Very few CEO’s will actually work with the database but they will often review the many reports created to represent the database’s table.

q     report seen by the CEO has much less detail then those seen by middle-level managers.

q     top-management wants a “big picture” view of how the company is doing in sales.

q     mid-level managers will want the details of what region is selling the most and/or what salespersons are performing the best and so on.

q     may produce many different reports from the same set of data.

q     use of report templates will help make existing and future reports much more adaptable.

Domain Aggregate Functions

n     Domain - A set of records.

n     Domain aggregate functions - Functions that provide statistical information for a domain

n     DLookUp function - used in the report template

n     syntax presented under the list of functions.

q     the first two arguments, “expression” and “domain”, are required. third one, “criteria” is optional. 

n      The syntax is the same for each of these functions, only the function name changes.

n      example DLookUp(“CompanyName”,”tblCorporate”,”ID=1”

q     DLookup function looks up and returns the CompanyName field value from the tblCorporate table when the ID field value is equal to 1

n     DLookup – returns value in the specified field

n     DMin(),DMax() – returns minimum or maximum value in specified field

n     DFirst(),DLast() – returns the value in specified field from first or last physical record

n     DAvg() – returns arithmetic average of values in specified field

n     DSum() - returns sum of values in specified field

n     DCount() - returns number of records with non-null values in specified field

Report Sections

n     Report Header Section - A section that appears once at the beginning of a report

n     Page Header Section - A section that prints at the top of every page of the report.

n     Detail Section - The main body of the report. It prints one time for each record in the report’s underlying record source (table or query).

n     Page Footer Section - The section that appears at the bottom of every page of the report.

n     Report Footer Section - The section that appears once at the end of the report.

Grouping Records in a Report

n    Group Header Section - A section that appears at the beginning of a new group of records. Used to show information that applies to the group, such as a group name or a picture.

n    Group Footer Section - A section that appears at the end of a group of records. Typically used to show calculations, such as a total or average of the records in the group.

Creating the Self Join Inner
(default)

n     report created will be based on query from next slide

q     good idea to create a query that contains the data wanted in a report and then design a report to layout the data as desired. 

n     Can Grow property – set to yes, enables section or control to grow vertically so all data visible

q     When set for a control in a section, Access automatically sets report section’s Can Grow property to Yes

n     Can Shrink property – set to yes, shrinks the section or control vertically to avoid blank lines

q     Setting these properties often involves trial and error.

n      design is very important but often it comes down to trying a report, looking at your results, and then making alterations accordingly.

Report Snapshot

n      Report snapshot is a file (.snp extension) contains high-fidelity copy of each page preserving two-dimensional layout, graphics, and other embedded objects of the report.

q     picture of a point-in-time, snapshot does not update as the data updates.

q     advantage of snapshot is provide a report to someone without them having Access installed to view it. (E-mail)

n      similar to creating a PDF to be used in Acrobat.

n      If  snapshot viewer not installed must install before viewing reports.

q     book shows you where to find the viewer on the web, also available on your Office CD

n      if the CD is necessary, you will be prompted to insert it into your CD drive.

Event Properties

n    how Event-driven programming works. 

q    When an action is associated to an Event, that action does not happen unless the event occurs.  If the event never occurs, the action never happens. 

q    Example the Click of the mouse:

n     An object is clicked on the screen and something happens.  The click of the mouse is the event which “triggers” the underlying action.

 Event Properties

n      Data events - occur when data is entered, deleted, or changed in a form or control. They also occur when the focus moves from one record to another.

n      Error and timing events - are used for error-handling and synchronizing data on forms or reports.

n      Filter events - occur when you apply or create a filter on a form.

n      Focus events - occur when a form or control loses or gains the focus.

n      Keyboard events - occur when you type on a keyboard.

n      Mouse events - occur in a form or in a control on a form as a result of a mouse action, such as pressing down or clicking the mouse button.

n      Print events - occur when a report is being printed or is being formatted for printing.

n      Window events - occur when you open, resize, or close a form or report.

Print Events

n      Format – occurs when Access determines what data goes in a report section, but happens before section is formatted for previewing or printing

n      NoData – occurs after Access formats a report for printing when report has no data, but before report printed

n      Page – occurs after Access formats a page for printing, but before page is printed

n      Print – occurs after Access has formatted data in a report section, but before section is printed

n      Retreat – occurs when Access must back up past one or more report sections on a page to perform multiple formatting passes

q     occurs after the section’s Format event, but before Print event

q     Allows undoing changes made during Format event for section

Converting Macros to VBA Code

n     Macros are easier to create, VBA code is executed faster.

q     VBA code allows you to add code that will handle errors, better way to automate user interface

n     Differences between functions and sub procedures

q     Functions return a single value

n      function does not ALWAYS return a value, this type of function is called a Void function.

q     sub procedure does not return a value.

n      store their code in a standard module

q     standard module differs from; say a form module, in that it is not tied to any particular object. (Avoids duplicate code)

n      Previously, created a form module and the code within that module applied specifically to that form.

Key Terms

n      Function Procedure - A procedure that returns a value, such as the result of a calculation.

n      Sub Procedure - A procedure that is a series of VBA statements that performs actions but does not return a value

n      Void Function - A function used to do work and not return a value.

n      Standard Module - Standard modules have general procedures that are not associated with any specific object in the database and are intended for frequently used procedures that can be run from anywhere within your database. (Prevents duplicate code)

n      Form Class Module - A module that is saved as part of a form and contains one or many procedures that apply specifically to that form.

The Running Sum Property

n    Running Sum property – used to calculate record-by-record totals or group-by-group totals in report

q    Can specify text box to display a running total

n      Can set the range over which to accumulate the values

n      If Running Sum property set to Over All, it counts the number in the first group and continues counting all records in each successive group, instead of starting over with each group, value accumulates to end of report

n      If Running Sum property set to Over Group, running sum value accumulates until another group encountered

Subreports

n      Subreport - is a report that is inserted in another report.

n      can create individual reports and then combine them into a single report.

n      One of the reports will need to be established as the main report.

n      Main Report – can be bound to underlying table, query or SQL statement, or it can be unbound

q     Unbound main report – can serve as container for unrelated subreports

q     Bound main report – must contain a field in its record source that is also a field in its subreport(s).

n      Subreports often used to show summary data related to the detail data

Page Numbering

n     Page property – specifies current page number when page is being printed

n     Pages property – specifies total number of pages in report

q     Page and Pages properties can be used in an expression, macro, or VBA

Example expression:

=“Page “ & Page & “ of “ & Pages

n     Report’s Page property is read-write at run time, means it can be reset at any time by using macro or code as the report prints

Adding Blank Rows to a Report

n    Access does not provide a method to insert a blank row in the middle of a Detail section

q    Can use VBA code and properties of controls and sections on report to insert blank rows

n      To add blank rows to a report, insert a blank unbound text box (txtSpacer) in detail section below other text boxes, set the Can Grow and Can Shrink properties of txtSpacer text box and the Detail section to Yes

n      For blank row to print, txtSpacer needs to contain “ “, causes text box to print, but nothing visible inside it

n      Can write VBA code to insert blank row after a certain number of records

VBA Terminology

n      If…Then…Else statement – conditionally executes a group of statements in VBA depending on results of an expression

n      Control structure – series of VBA statements that work together as a unit (sequence, selection, iteration)

n      VBA statement – is a unit that expresses one kind of action, declaration, or definition in complete syntax

n      VBA expression – combination of keywords, operators, variables, and constants that yields a string, number, or object

n      Operators – used to perform arithmetic calculations, combine strings, and perform logical operations

q     Mod Operator – arithmetic operator used to perform modulo division, divides two numbers and returns only the remainder.

IFS 110 Computer Assignment 5

Return to IFS 110 Notes!