Microsoft Office Access 2007 Class 5 Reports Page 1

Document Sample
Microsoft Office Access 2007 Class 5 Reports Page 1 Powered By Docstoc
					Microsoft Office Access 2007                                                                  Class 5: Reports

Understanding Reports

   Reports let you view and print information from your database. They can be based on, and show
    data from, tables or queries.
   Reports look a lot like forms. Most of the tools we used to create and customize forms work the
    same way with reports.
   Unlike forms or queries, reports are “output only”: nothing you do with a report can change the
    data in your tables.

The Create Ribbon: Reports Group

                                       Select a query (or table) and                        Create a report
                                           click. Access generates a                        from scratch using
                                        report based on that query.                         Design View.

                                            Use the Report Wizard to
                                           create a report step by step,
                                             with prompts by Access.

Create a report using the Report button

1. In the Navigation Pane, click to select the query to base the report on.
2. Click the Create tab to view the Create Ribbon. Click the desired Report button.
3. The new report opens in Layout View.

Create a report using the Report Wizard

1. On the Create Ribbon, click the Report Wizard button.
2. On the wizard screen, use the drop down button to select each table or query containing fields you
    wish to have in your report. Use the        and/or            buttons to move some or all fields to the
   Selected Fields list at the right; use the    and/or       buttons to remove some or all fields. Click
   the Next button.
3. On the next screen, choose up to four layers of grouping levels. Grouping levels let you group and
   summarize data based on the fields you select.
            a. Move fields up and down in priority by using the priority arrows.
            b. Use the Grouping Options button to customize grouping intervals for each field, if
                desired (ex: group dates by quarter, month or year).
    Click Next.

                                                                                                    Page 1 of 8
Microsoft Office Access 2007                                                              Class 5: Reports

4. On the next screen, choose up to four fields to sort by. Use the Summary Options button to add
   totals and other summaries to numerical fields.
            a. Click the Summary Options button to
                open the Summary Options box.
            b. Click the check boxes for summaries
                you wish to calculate
            c. Select "Detail and Summary" to see
                individual data as well as the summary
                value, or "Summary Only" to hide the
                individual data and see only the
            d. Click OK to close the Summary
                Options box and return to the Sort Order step of the wizard.
   Click Next.
5. On the next screen, choose a layout (stepped, block, or outline) and an orientation (portrait vs.
   landscape) for your report. Click Next.
6. On the next screen, choose a style (AutoFormat).
7. On the final screen, name your report. Click Finish. Your report will be displayed in Design View.

Report Sections:

Report Header: Printed once at the beginning of the report. Use the report header for information that
might normally appear on a cover page, such as a logo, or a title and date. The report header prints
before the page header. When you place a calculated control in the report header, the value is
calculated for the entire report. For example, placing a control that uses the Sum function in the report
header calculates the sum for the entire report.

Page Header: Printed at the top of every page. Use a page header, for example, to repeat the report title
on every page.

Group Header: Printed at the beginning of each new group of records. Use the group header to print the
group name. For example, in a report that is grouped by product, use the group header to print the
product name. When you place a calculated control that uses the Sum function in the group header, the
sum is for the current group.

Detail: Printed once for every row in the record source. The Detail section is where you place the
controls that make up the main body of the report.

Group Footer: Printed at the end of each group of records. Use a group footer to print summary
information for a group.

Page Footer: Printed at the end of every page. Use a page footer to print page numbers, date and time
stamps, etc.

Page 2 of 8
Microsoft Office Access 2007                                                                Class 5: Reports

Report Footer: Printed once at the end of the report. Use the report footer to print report totals or other
summary information for the entire report.

Note: In Design view, the report footer appears below the page footer. However, when the report is
printed or previewed, the report footer appears above the page footer, just after the last group footer
or detail line on the final page.

Add or remove sections

As in forms, turn the Report Header/Footer and the Page Header/Footer on and off using
tools in the Show/Hide group of the Arrange Ribbon; be aware that if you turn off a
header/footer with controls in it, the controls will be deleted.

Summarize report data using grouping sections

1. On the Design Ribbon, click the Group and Sort button in the Grouping & Totals
   group. The Group, Sort and Total control panel appears at the bottom of the screen.

2. Click the More button to see all the options available to customize the grouping level. Use the
   dropdown arrows to select desired options. In the example below, designers can customize the sort
   order, set the time frame for the date grouping (by year, by quarter, by month, etc.), determine
   which field(s) to total, add a title, turn on a header or a footer section, and choose whether to
   minimize the number of page breaks within a group.

3. Click the Add a group button to group on another field. Access will nest the groups according to
   their group level. The first field you group on is the first and most significant group level; the second
   field you group on is the next group level; and so on. Access can handle up to ten such levels.
4. To sort records without grouping them, click Add a sort and select the field(s) to sort by.
5. Remove a grouping level by clicking the       at the right of that level’s description.
6. To automatically add total-calculating controls to all grouping footers, right click the field you are
   summarizing in the Detail section and choose Totals from the drop-down menu. Select Sum (or
   another aggregating function). Calculated controls will be created in each section which will subtotal
   the selected field for that section.

                                                                                                Page 3 of 8
Microsoft Office Access 2007                                                               Class 5: Reports

Resize report sections

As in forms, resize report sections by dragging their edges with the mouse.

The Design Ribbon

Control Types

Access reports utilize three types of controls:

Bound control: A control whose data source is a field in a table or query. Bound controls display values
from fields in your database. Ex: a text box displaying the Customer ID number.

Unbound control: A control that doesn't have a source of data. Unbound controls include labels, shapes
such as lines or rectangles, and pictures. Ex: a label displaying the title of a report.

Calculated control: A control whose source of data is an expression rather than a field. Ex: a text box
with the following as its control source: =*Price+**Quantity+ or =*FirstName+&” “&*LastName+

Add controls to a report in Design View

1. Open the report in design view.
2. Select the desired type of control from the Controls group on the Design Ribbon, and click on the
   report background where you want the control to appear.
3. To bind the control to a data source, select the control and click the Property Sheet icon in the Tools
   group on the Design Ribbon. Choose a control source from the dropdown list under the Control
   Source property.

Alternative method for text boxes:

1. Click the Existing Fields button in the Tools group on the Design Ribbon.
2. Drag text boxes from the listed fields and drop them on your report.

Add a graphic to a report

1. In Design view, click the Image button ( ) in the Controls group of the Design Ribbon, then click
   the form where you want the image placed.
2. The Insert Picture dialog box will appear; browse to locate the desired image and double click it to
   insert it.
3. Right-click the image and choose Properties. Under Size Mode, choose one of the following:
             a. Clip: shows as much of the image (at actual size) as will fit in the image control
             b. Stretch: Sizes and distorts the image so it all fits in the image control. Picture may be

Page 4 of 8
Microsoft Office Access 2007                                                             Class 5: Reports

            c. Zoom: Sizes the image to best fit the control without being distorted. May leave blank
               space at the sides of the control.

Access also offers a Logo button in the Control group on the Design Ribbon. Use this to automatically
create an image control in the header of the selected section. Choose an image in the same way as

The Page Layout Ribbon

Printing Options for Reports

Because reports are intended to be printed, you can control page-related options in a way you cannot
with forms. Use the Page Layout Ribbon to set margins, etc.

How Printing and Grouping Work Together

In the Grouping Levels stuff earlier, some options pertain to printing. Here they are:

Keep group together: This setting determines how groups are laid out on the page when the report is
printed. You may want to keep groups together as much as possible to reduce the amount of page
turning that is needed to see the entire group. However, this usually increases the amount of paper
needed to print the report, because most pages will have some blank space at the bottom.

Do not keep group together on one page: Use this option if you are not concerned about groups being
broken up by page breaks. For example, a group of 30 items may have 10 items on the bottom of one
page and the remaining 20 items at the top of the next page.

Keep whole group together on one page: This option helps minimize the number of page breaks in a
group. If a group cannot fit in the remaining space on a page, Access leaves that space blank and begins
the group on the next page instead. Large groups may still span multiple pages, but this option
minimizes the number of page breaks within the group as much as possible.

Keep header and first record together on one page: For groups with group headers, this ensures that the
group header will not print by itself at the bottom of a page. If Access determines that there is not
enough room for at least one row of data to be printed after the header, the group begins on the
following page.

Force page breaks before or after sections

Right click on the section bar for the section you want to display on a new page, and choose Properties.
Click in the Force New Page row and choose “Before Section”.

                                                                                              Page 5 of 8
Microsoft Office Access 2007                                                             Class 5: Reports

The Arrange Ribbon

Selecting, moving, aligning and sizing controls:

Exactly the same as in forms!

   Click controls (anything!) to select control. Handles will appear.
   Drag the sizing handles to resize controls.
   Drag the extra-large handle to move a control independently from any associated controls (like
   Drag controls by an edge (NOT a handle) to move associated controls (labels and text boxes)
   Select groups of controls with the Lasso or by holding the Shift key while clicking them.
   Align controls from the Control Alignment group on the Arrange Ribbon
   Adjust vertical and horizontal from the Position group on the Arrange Ribbon
   Adjust the size of controls from the Size group on the Arrange Ribbon
   Format fonts and colors of controls using the tools in the Font group on the Design Ribbon

Practice Assignment:

1. Create a contributions report using the Report Wizard:
   a. Click the Contribution Report query, then click Report Wizard on the Create Ribbon. Add all
       fields to the report; do not add grouping levels or sorting. Choose a Tabular layout and Portrait
       orientation; select the Module style, and name your report Contribution Report. Click the
       “Modify the report’s design” button, then click Finish.
   b. In Design view, change the label in the Report Header to read “NEO Outreach Contribution
       Report”. (Resize if necessary.) Change the labels in the Page Header section to Donor Name,
       Total Contribution, and Average Donation.
   c. Open up the Report Footer section and place an unbound text box (and its label) in this section
       using the Toolbox. Change the label to read “Total Donations for Period”. Format the label and
       text box as desired. Align the text box under the ‘SumOfAmount’ text box.
   d. View the Property Sheet for the text box. In its Control Source property, type
       =Sum([SumofAmount]). In the Format property, choose Currency. Close the Properties
   e. Repeat steps c and d to create a second calculated control aligned below the ‘AvgOfAmount’
       text box to calculate average giving. Use the expression =Avg([AvgofAmount]) and label it
       “Average Donation for Period”.

Page 6 of 8
Microsoft Office Access 2007                                                             Class 5: Reports

    f. Save the report, then click the View button. Since the report is based on a parameter query, you
       will be prompted to enter dates. (This is the "period" we've been referring to above.) Use the
       range: 9/1/2003 to 12/31/03.
2. Create a report to generate printed contribution statements for each donor:
   a. Use the report wizard to create a report based on the Donor Statement query. Choose the
       following fields, in this order: Donor ID, LastName, FirstName, Address, City, State, Zip,
       Contribution Date, Payment Method, Project ID, Amount. View data by donors, and add a
       grouping level for Donor ID. On the Sorting screen, click Summary Options and check the Sum
       box for the Amount field. Use the Block layout, the Trek style, and Portrait orientation. Name
       the report Donor Statements. Click Finish.
   b. Switch to Design view, and click the Group & Sort button on the Design Ribbon. Access will have
       automatically added Last Name as a grouping level; delete this grouping level and its controls.
       Add a sort by Last Name and one for First Name below it. Make sure the Donor ID grouping level
       is showing both a header and footer section.
   c. Delete the Report Header and Footer section and all their controls using the button in the
       Show/Hide group on the Arrange Ribbon. Remove the Layout from all the controls in the Detail
       and Page Header sections.
   d. Delete the labels in the Page Header section except those for Contribution Date, Payment
       Method, Project ID and Amount. Open up the Page Header section to about 1½ inch tall, and
       use the Label tool from the Controls group on the Design Ribbon to add a large rectangular label.
       Type the following text in the label: (Holding down the Shift key while pressing Enter lets you
       place more than one line in the label box.)

                                                NEO Outreach
                                                3512 Darrow Road
                                                Stow, OH 44224

       Change the size of the type to 24-point. Change the font color to red. Resize the label if
    e. Open up the Donor ID Header section to about 2 inches tall. Add an unbound text box to the
       Donor ID Header section, and delete the label that “came with it”. Open the Property Sheet and
       type the following expression: [FirstName]&" "&[LastName] in the new text box’s
       Control Source property. Close the Property Sheet, and resize the control so the full name will
       be visible. Delete the LastName and FirstName text boxes from the Detail area. (Make sure the
       concatenated text box works - switch to Layout or Report View and you should see the first
       name and last name, separated by a space. Switch back to Design View when done.)
    f. Cut the Address, City, State and Zip text boxes from the Detail area and paste them in the
       Donor ID Header section. Arrange the fields to form an address block, resizing them as needed.
       Switch to Layout View any time it seems helpful! (For “extra credit”, create a concatenated
       control to hold the city, state and zip code information similar to the one holding the first and
       last names.) . Use the Rectangle tool to draw a rectangle around the address block, and send it
       behind the address block with the Send to Back button in the Position group on the Arrange
       Ribbon. Delete the Donor ID field from the Detail area.

                                                                                             Page 7 of 8
Microsoft Office Access 2007                                                            Class 5: Reports

    g. Draw a straight line at the very bottom of the Donor ID Header section. Using the Property
       Sheet, make sure it is straight (height=0) and make it 2 points wide. Copy the line and paste a
       copy in the Donor ID Footer section; arrange it at the top of the section, lined up below the
       original line.
    h. Cut the labels for Contribution Date, Payment Method, Project ID, and Amount out of the Page
       Header section and paste them in the Donor ID Header section. Arrange them just above the
       line. In the Detail area, align the appropriate text boxes below the labels. Resize text boxes so
       they are large enough to display their data. Align labels and text boxes so they look tidy.
    i. Add a total calculation by right-clicking the Amount text box in the Detail area and choosing
       TotalSum. If the calculated control appears anywhere other than the Donor ID Footer section,
       drag it to that section and align it under the Amount control in the Detail section.
    j. In the Donor ID Footer section, delete the long text box beginning “Summary…”. Change the
       Sum label to read Total Contribution and drag it next to the calculated control created in the
       previous step.
    k. In the Page Footer section, delete the Page Numbering text box (since each page of the printout
       will be mailed to an individual donor.) Resize the date text box to about 2 inches wide, and drag
       it to the middle of the Page Footer. Add a label on either side. The one on the left should read
       “Statement Date:”, and the other should read, “Thank you for your generous gift!”. Format as
    l. Resize the report width to
       about 6.5 inches, moving
       or deleting any stray
       controls that are in the
    m. Force each donor’s data to
       be on a separate page by
       opening the Property
       Sheet and selecting the
       Donor ID Header bar. Set
       the Force New Page
       property to “Before
       Section”. Close the box.
       Your final report should
       resemble the sample at
       the right (except it should
       fill a full page height). Print
       or use print preview to
       check your work.

Page 8 of 8