Forms & Reports:
Chart Wizard

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

A picture is worth a thousand or so datasheet cells. Many kinds of data are more easily understood as a chart. Microsoft Access has a Chart Wizard to help you.

Print Preview: Chart Report

Example from Language Arts Classes:
Total number of each letter grade
for the year for 3 subjects

TipAccess is not the most user-friendly program for creating charts, especially complex charts! You may find it easier to export your data to Excel and create an Excel chart. Then embed the chart in a report header or footer.

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 ReportsTo subtopics
    Special Forms & Reports Arrow: subtopic open
    Icon: StepSwitchboard Form
    Icon: StepCustom Dialog
    Icon: StepLabel Wizard
    Icon: StepChart Wizard
    Icon: StepMultiple Columns
    Icon: StepSubforms
    Icon: StepSubreports
    ExercisesTo subtopics


Chart Wizard

The Chart Wizard walks you through the choices to create a chart from data in a single table or query. After the wizard is done, you can change the source to include other tables or queries. If you want to use a Value List instead of an existing table or query, you should insert a default chart and then modify the Row Source property of the chart.

Start Wizard: There are two ways to open the Chart Wizard, depending on whether you want to add a chart to an existing report or you want to create a report containing just the chart.

  • Button: New Object - ReportMenu: Insert | ChartIn Report Design View, from the menu-  Insert | Chart...
  • Open the New Report dialog and pick Chart Wizard.  You will get a new report, containing just the chart.

Source: Depending on which method you used to start the wizard, you will either see a Chart Wizard window or the New Report dialog. Pick one table or query as the source of your data.

Chart Wizard: Pick table or query  Dialog: New Report - Chart Wizard

TipA suitable table or query must already exist to start you out! You can add others to the underlying query only after the wizard finishes. You may want to create a special query or table first so that your data is ready for the chart, as was done for this example.

Chart Wizard: Step 1 = Pick fieldsFields: Pick which fields to use in the chart.

Chart Wizard: Step 2 = Pick type of chartChart type: Pick the type of chart. There are fewer options than in Excel.

Chart Wizard: Step3 - Set up seriesData Layout: The Chart Wizard makes a guess as to which data belongs where on the chart. You can change that.

Chart Wizard: Step 3 - Preview of seriesButton: Preview chartThe Preview Chart button opens a more accurate display than what shows on the wizard page itself.

For this example, I had to drag the Spelling and English items from the right and drop them in the area to the upper left of the sample chart to create several series of columns.

Chart Wizard: Step 4 - Name the reportChart Title: Enter a title for the chart. This is not the name for the report! Just what will show on the chart itself.

Chart Wizard: DoneThe default chart size is rather small on the page. You will likely want to resize it in Report Design View unless you are inserting a chart into the Detail section.

Editing a Chart

Once the wizard has finished, you cannot get back to the wizard pages. You must either recreate the chart with the wizard or edit it in Report Design View. It looks quite different in this view! Not right at all!!

Chart Design View

WarningReport Design View uses default data and will never show your actual data. Only the chart title is from what you just chose in the wizard!

You can resize the chart by selecting it and dragging its handles. You can drag it to a new position on the page.

To change the data underlying the chart, go to the Properties dialog for the chart control and in the Source property, open the Query Builder and edit the query as much as you wish.

TipIf you want to show a static chart that is not based on Access data, create it in Excel and paste it to your report.

Chart in Microsoft Graph for formattingFormat chart features with Microsoft Graph: Double-click the chart and chart's border changes and the toolbar also changes, showing that the chart is now in Microsoft Graph. A datasheet window may appear, but it and the chart show only the default sample data. You can edit the chart's title and format chart parts like the axes and legend from this window. You can change the fonts, font sizes, and alignment of axes labels. You can change the colors of the series bars and hide or show the legend.

WarningDefault Data always in Report Design View: In Report Design View, the chart always uses the default data from Microsoft Graph. Access cannot figure out the actual data until it formats the chart for Print Preview. Any changes to the datasheet values in the chart's datasheet while in Microsoft Graph will show in Report Design View but will NOT show on the actual chart in Print Preview.  The datasheet values are replaced when Access formats the report. This can be quite confusing!

TipCheck changes in Print Preview: When you make formatting changes in Report Design view, you will not see what will print. You must check the print preview carefully to see the effect of your changes. You may need a different font size or alignment for the actual text to fit well.

Icon: Step-by-Step 

Step-by-Step: Chart Wizard

 Icon: Step-by-Step

What you will learn:

to use the Chart Wizard

Start with:  Class diskresource files, worldtravel.mdb from the previous lesson

You are going to create a report containing a chart of the total price of trips sold for each agent. The Chart Wizard does not allow you to use more than one table, so you will have to edit the query that the wizard creates to get an understandable chart.

  1. Button: New Objects - ReportFrom the menu select New Objects | Report.
    The New Report dialog opens.
  2. Dialog: New Report - Chart Wizard, Trips tableSelect Chart Wizard and as the source, the table Trips.
  3. Click on OK.
    The Chart Wizard opens to the page about fields.
  4. Chart Wizard: Choose fieldsClick on AgentID and Price and then on the > button to move them to the right column, Fields for Chart.
  5. Click on Next.
    The wizard opens the page for chart type.
  6. Chart Wizard: Choose chart typeClick on the 3-D column chart, in the top row.
  7. Click on Next.
    The Chart Wizard makes a guess as to how you want the data displayed, AgentID across the bottom and a total of prices for that AgentID in the column. That's right!
  8. Chart Wizard: Data LayoutButton: Chart PreviewClick on the Preview Chart button  to see how your chart will look.
    Chart PreviewHmmm. In the preview, you can see the actual axes and columns. The legend seems useless this time.
    The only problem is that the labels across the bottom are numbers instead of names. That is because the Trips table contains only the AgentID number. You will have to change the source to fix this issue, but it's not all that hard.
  9. Close the preview.
  10. Click on Next.
    The Chart Wizard wants a title for the chart.
  11. Chart Wizard: TitleType Trip Totals by Agent as the title.
  12. Click on the option button
  13. Leave the default choices ,Yes, display a legend and Open the report with the chart displayed on it.
  14. Print Preview: Chart reportClick on Finish.
    The report opens with a small chart.
  15. Icon: Save Save the report as Chart-Trip Totals by Agent

Working in Report Design View

A chart looks different in design view since it can only show the default sample data. There are some tricks to formatting the chart, too. To edit the chart's features, you must open the chart in Microsoft Graph.

  1. Report Design View: Chart with default sample dataSwitch to Report Design View.
    The chart does not look the same! Remember, the chart will always use the default sample data when you are in design view.
  2. If necessary, click on the chart to select it.
    The handles should show at the corners and center of each side.
  3. Move your mouse pointer over the bottom right corner of the chart until the pointer changes to the diagonal Resize shapePointer: Resize Diagonal.
  4. Report Design View: dragging chart largerDrag down and to the right until the black bars on the ruler are at 5" wide.
  5. Switch to Print Preview.
    The chart is noticeably larger but it is not centered.
    Print Preview: Chart Report - chart larger
  6. Moving the chartSwitch to Report Design View.
  7. Move your mouse over the chart until the pointer turns to the Move shape Pointer: Move (open hand), an open hand.
  8. Drag the chart to the right.
    The black bar on the ruler extends as you drag.
  9. When the black bar is at the 5.5" mark on the ruler,  drop.
  10. Print Preview: after moving chart in Design ViewSwitch to Print Preview.
    Is your chart centered on the page? Try again.
  11. Switch to Report Design View.
    The remaining changes cannot be made directly in Report Design View. You must open the chart in Microsoft Graph.

Working in Microsoft Graph

Microsoft Graph is a program that comes with Microsoft Office. It opens only when called upon by an Office program. You will remove the legend and change some formatting of the chart.

  1. Microsoft Graph: Chart with default sample dataDouble-click the chart.
    Border changes to diagonals when in Microsoft GraphThe border of the chart changes to diagonals.
    The toolbar and menu change.
    You may see a datasheet below the chart.
    All these changes are quite surprising when you don't expect them.
  2. Menu: Help | About Microsoft GraphClick on the Help menu.
    The last item is "About Microsoft Graph". This tells you what program you are in.
    You can now make some changes to the chart.

Remove Legend

  1. Microsoft Graph: Legend removedMicrosoft Graph: Legend selectedClick on the Legend to select it.
    Handles appear.
  2. Press the Delete key.
    Bye-bye, Legend. The chart expands to take up the extra space.
    With the sample data, this does not look like a good choice. Remember that your chart only has one set of bars, so the legend is not really needed.

Change Column Color

  1. Right Click Menu: Format Data Series...Right click on the first column at the left of the chart.
  2. From the popup menu, select  Format Data Series...
    The Format Data Series dialog appears.
  3. Dialog: Format Data Series - Pattern, light orangeOn the Patterns tab, click on the light orange square in the fifth row from the top.
  4. Click on OK.
    In the chart, the whole series of columns changes color.
    Microsoft Graph: column color changed
  5. Print Preview: with orange columnsClick off the chart to get out of Microsoft Graph and back to Report Design View.
    The menu and toolbar change back to Access.
  6. Switch to Print Preview.
    How do your changes look with the actual data?
    The legend is gone and the columns are in light orange, like the first series in the sample data. Success!

Change Fonts and Alignment

  1. Right Click Menu: Format axisSwitch to Report Design View and double-click the chart to get back into Microsoft Graph.
  2. Right click on the vertical axis and from the popup menu select  Format Axis....
     The Format Axis dialog appears.
  3. Dialog: Format Axis - TahomaOn the Font tab, change the font to Tahoma.
  4. Click on the Number tab.
    Since the values on the vertical axis are total prices, it makes sense to format the labels as money.

  5. Dialog: Format Axis - NumberChange the Category to Currency.
  6. Reduce the Decimal places to 0.
  7. Click on OK.
    You are back in Microsoft Graph with a new look for the vertical axis labels.
    Microsoft Graph: vertical axis formatted, Tahoma, Currency
  8. Dialog: Format Axis - 30 degreesRight click on the horizontal axis labels and from the popup menu select  Format Axis....
    The same dialog appears as before.
  9. On the Fonts tab, change the font to Tahoma.
  10. On the Alignment tab, drag the angle pointer up to 30 degrees.
  11. Click on OK.
    The labels for the horizontal axis are now angled. It is not as clear that the font has changed also.
    Microsoft Graph: horizontal axis formatted
  12. Print Preview: changes to horizontal axisClick off the chart to get out of Microsoft Graph and back to Report Design View.
    The menu and toolbar change back to Access.
  13. Switch to Print Preview.
    How do your changes look with the actual data? The labels across the bottom of the chart look funny. They are angled, but that makes them harder to read.
    Why were you asked to angle the labels? A bit later you will edit the chart and the new labels won't fit in the space unless they are angled. Just thinking ahead a bit!

Changing the Source

Let's do something about those ID numbers now. Those AgentID numbers are just not very helpful. We really need to see the agent's names. You will have to change the source for the chart, using the Query Builder in the Properties dialog.

  1. Dialog: PropertiesSwitch to Report Design View.
  2. Click on the chart to select it. (Do not double-click!)
  3. If necessary, open the Properties dialog by clicking the Properties button.
    The title bar for the Properties dialog shows that it is for an Unbound Object Frame, name OLEUnbound0.

    tip Numbering Objects: The default names for objects include a number, starting with zero.
  4. Query BuilderClick in the property Row Source and then on the ellipsis button.
    The Query Builder opens and show the Trips table and the two fields that you chose in the Chart Wizard.
  5. Click the Show Table button Button: Show Table (2003) on the toolbar.
  6. Click on the Queries tab and select the query QFullName-Staff.
  7. Click the Add button.
     The query appears in the Query Builder.
  8.  Close the Show Table dialog.
  9. Query Builder: FullName columnDrag the field AgentID from the Trips list and drop it on the field StaffID in the list of fields for QFullName-Staff.
    A join line appears.
  10. Drag the field SFullName and drop it in the grid.
  11. Query Builder: DatasheetRun the query by clicking the Run button Button: Run.
    The datasheet shows 3 values, one for each agent, and includes their names. Hurrah! Now we have actual names to use.
  12. Query Builder: SFullNameSwitch to the Query Design View.
  13. Delete the column AgentID.
  14. Drag the SFullName column to the left of Price.
  15. Close the query by clicking the Close button, not by saving.

    A message appears to ask whether you want to save the changes. You do!
    Message: Do you want to save the changes made to the SQL statement and update the property?
  16. Print Preview: with namesClick on Yes.
    You are back in Report Design View with no apparent change.
  17. Switch to Print Preview.
    There is definitely a change!

    Those agent names are in view and angled. That's what we wanted.
    The columns have shifted location. That is because the order is based on the names, not the ID numbers.

    The vertical axis labels are crowded together. The names are taking up a lot of space and the chart cannot resize itself larger. It must resize chart parts within the dimensions of the chart. You need to help out.
  18. Switch to Report Design View.
  19. If necessary, click on the chart to select it. (Do not double-click.)
  20. Move your mouse pointer over the handle in the middle of the bottom edge of the chart.
    The point should have the Resize Vertical shape Pointer: Resize Vertical, and not the shape for resizing the section Pointer: Resize Section Vertically.
  21. Chart Design: taller chartDrag down to make the chart taller.
    The Detail section automatically enlarges to hold the chart.
  22. Switch to Print Preview.
    If your chart is not tall enough for easy reading, try again.
  23. Icon: Save Save the report. (Chart-Trip Totals by Agent)
  24. Print Preview: taller chartAdd a label containing your name to the Page Header at the left.
  25. Icon: Print Print.