Forms & Reports:
Report Wizard

 Title: Jan's Illustrated Computer Literacy 101
Did you want: Working with Databases: Access 2007, 2010, 2013, 2016

There are several ways to create a printable report of data from your tables and queries.

Methods to Create a Report

  • AutoReport

  • Report Wizard

  • Report Design View, manually adding controls

You have already used the AutoReport feature in Project 2: Access Basics. An AutoReport is always based on only one table or query.

In this lesson you will use the Report Wizard, which walks you through some choices. You can include fields from more than one table or query. The report that the wizard produces is not all that great. Later you will create a report directly in Design View.


Where you are:
JegsWorks > Lessons > Databases

Before you start...

Project 1: Intro

Project 2: Access Basics

Project 3: Tables & Queries

Project 4: Forms & Reports Arrow: subtopic open
    Import/Export/LinkTo subtopics
    Designing FormsTo subtopics
    Designing Reports Arrow: subtopic open
    Icon: StepReport Wizard
         Printing Reports
    Icon: StepCreate Report Manually
    Icon: StepGroup Report
    Icon: StepTotals - Simple
    Icon: StepTotals - Calculated
    Icon: StepSharing Reports
    Special Forms & ReportsTo subtopics
    Summary
    Quiz
    ExercisesTo subtopics

Search  
Glossary  
Appendix

Report By Wizard

The Report Wizard takes you through the choices to create a report. It works better for simple reports.

Methods to start the Report Wizard:

  • Link in the Database Window, in the Reports list Link: Create report by using wizard
     
  • Dialog: New Report - Report WizardButton: New Object - list opened and Report selectedToolbar button: New Object | Report | Report Wizard  
     

Steps in the Report Wizard:

Report WizardIcon: Mouse clickClick each step below to see the wizard's page at the right.

  • Step 1: Source for the data - one or more tables or queries and which fields to include
  • Step 2: Group data on one or more fields. The Grouping Options button opens a dialog where you can choose an interval to group on, such as each month or on the first letter.
  • Step 3: Sort on up to 4 fields. The Summary Options button opens a dialog that offers to calculate Sum, Avg, Min, and Max for fields that are Number data type.
  • Step 4: Layout, one of 6 layouts (shown below) . Choose Portrait or Landscape orientation for the paper.
  • Step 5: AutoFormat styles, one of 6 pre-installed styles plus any previously saved custom AutoFormat styles (choices shown below)
  • Step 6: Title for the report, which will be the name of the report also
     

Report Wizard: Layouts

The Report Wizard offers 6 different layouts, plus the option to use Portrait or Landscape orientation.

Stepped report layout

Block report layout
Stepped Block

Outline 1 report layout

Outline 2 report layout
Outline 1 Outline 2
Align Left 1 report layout Align Left 2 report layout
Align Left 1 Align Left 2

In the Outline types, group headings are indented in stair-step fashion. In the Align Left types, group headings are all aligned at the left. You won't see the difference unless you have two or more grouping levels.

Report Wizard: AutoFormat Styles

Access comes with 6 pre-designed formatting styles for reports. These are quite different from the styles for forms. An AutoFormat style can be applied from the Report Wizard or later from the AutoFormat dialog. (Format | AutoFormat...)

After applying a style you can, of course, modify the report as you wish. You can even use the AutoFormat dialog to save your new style with a new name (a GOOD idea) or overwrite an existing style (NOT recommended!!)

An AutoFormat style includes formatting for the Report header/footer, Page header/footer, Group header/footer, Detail section, controls and labels. You can customize the background, font size, font color, borders, shadows or other special effects, and you can add decorative images to section headers or footers. It is not usually a good idea to add an image to the Detail section since it would appear with every record in the print-out.

Report Layout: Stepped

 Report AutoFormat: Casual
Bold Casual
Report AutoFormat: Compact Report AutoFormat: Corporate
Compact Corporate
Report AutoFormat: Formal Report AutoFormat: Soft Gray
Formal Soft Gray

If you delete or overwrite a standard AutoFormat style, it is not obvious how to get it back.
Icon: TroubleProblem: Missing or Changed AutoFormat

Icon: Step-by-Step 

Step-by-Step: Report Wizard

 Icon: Step-by-Step

What you will learn:

 to use the Report Wizard to create a report

Start with:  Class disk,  resource files, worldtravel.mdb

  1. Database Window: Reports - Create report by using wizatdIf necessary, open the World Travel database that you have been creating in this project.
     
  2. In the Database Window, click on Reports at the left and then double-click on the link Create report by using wizard that appears at the right.
    The Report Wizard opens.
     
  3. Report Wizard: step 1 - Source and fields to useStep 1: Source
    Select the table Clients as the source.
     
  4. Click the >> button to move all of its fields to the report.

    TipAdditional tables/queries: At this point you could select other tables or queries from the list and add some or all of their fields to the Selected Fields list. Not this time.
     
  5. Click on Next.
     
  6. Report Wizard: step 2 - GroupingsStep 2: Groups
    Scroll the list of fields and select the field Country as the first grouping level.
     
  7. Click the > button to change the sample document to show the group heading.
     
  8. Select the field PostalCode and click the > button.
    Now you have two grouping levels.
    Your records will be grouped by country and within each country, grouped by postal code.
     
  9. Click on Next.
     
  10. Report Wizard: step 3 - SortingStep 3: Sorting
    Sort Ascending     on LastName, FirstName, MiddleName, in that order.
     
  11. Click on Next.
     
  12. Report Wizard: step 4- Layout and orientationStep 4: Layout
    Select     the Stepped Layout and Portrait Orientation.
     
  13. Leave checked the box, Adjust the field width so all fields fit on a page.
     
  14. Click on Next.
     
  15. Report Wizard: step 5: AutoFormat styleStep 5: AutoFormat Style
    Select     the style Bold.
     
  16. Click on Next.
     
  17. Report Wizard: step 6 - TitleStep 6: Title
    Type     for the title of the report
     Clients-by country, postal code
     
  18. Click on Finish.

    Report Print Preview: Clients-by country, postal code - initial, Portrait orientationThe report opens in Print Preview.
     
  19. Inspect the pages.
    Something is wrong! It takes 4 pages to get all of the fields for one record. Only 3 records fit on a sheet! What happened??

    Perhaps Landscape view will be better.
     
     
  20. Dialog: Page Setup - Page tab, Landscape orientationFrom the toolbar, click on the Setup button.
     
     
  21. Click on the Paper tab and then on the option for Landscape.
     
  22. Click on OK.
    Not any better! Even in Landscape orientation, the paper is just not going to be wide enough to hold all of these fields in a single row.

    Report Print Preview: Clients-by country, postal code - initial, Landscape orientation 
    Happily, we are not stuck with just one row! Report Design View lets you rearrange the controls much closer together!

    Icon: QuestionEvaluate: How useable is this report? Why did it get so spread out?

    Not very useable! It's a bad idea to spread data from a single record over different pages if you can possibly get around it. The wizard stuck all of the controls in a single row. Not too smart, in this case.

    The wizard had to set the height of the Detail section taller enough for the Photograph control.  That leaves a LOT of empty white space. Some fields are blank, but the report leaves the space for them anyway. Another source of empty space.

    This report is not useable at all. Apparently the Report Wizard is not so good when there are many fields or when there are OLE controls.
     

  23. Icon: Save Save your changes and close the report. (Clients-by country, postalcode)
     
  24. In the Database Window, rename the report as Clients-wizard.
    The title control in the report does not change.

Report Wizard: No Photo

Clearly with the Photograph field included, the Report Wizard did not produce an acceptable report. What if we leave that field out?

  1. Start the Report Wizard again.
     
  2. Make the same choices as before except do NOT include the Photo field in Step 1 and use a different title in Step 6.
    • Step 1: Clients table. All fields except Photo.
    • Step 2: Group on Country and PostalCode fields.
    • Step 3: Sort Ascending on LastName, FirstName, MiddleName.
    • Step 4: Stepped layout and Portrait orientation
    • Step 5: Bold style
    • Step 6: Title = Clients-no photo
       
  3. Report Print Preview: Clients-no photo, PortraitInspect the print preview.
    It still takes 4 pages to see all the data for one record, but at least there are more records on a page!
     
  4. Report Print Preview: Clients-no photo, LandscapeUse Page Setup to change to Landscape orientation.
    Now it takes 3 pages to get the controls for one record.


     

    Icon: QuestionEvaluate: How useable is this report?

    It's a little better. You can see the groupings. But it is not good enough!
     

  5. Switch to Report Design view.
     
  6. Add a text label to the center of the Page Footer with your name.
     
  7. Icon: Print Print the first 3 pages.
     
  8. Icon: Save Save the new report. (Clients-no photo)
     
  9. In the Database Window, rename the report as Clients-wizard-no photo.

    It's a good thing we have more lessons ahead so we can learn how to do this right!