Forms & Reports:
Chart Wizard

Step-by-Step: Chart Wizard

---

What you will learn:

to use the Chart Wizard

---

Start with:  ,  resource 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. From the menu select New Objects | Report.
   The New Report dialog opens.
    
2. Select Chart Wizard and as the source, the table Trips.
    
3. Click on OK.
   The Chart Wizard opens to the page about fields.
    
4. Click 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. Click 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. Click on the Preview Chart button  to see how your chart will look.
   Hmmm. 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. Type 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. Click on Finish.
    The report opens with a small chart.
     
15. 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. Switch 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 shape.
    
4. Drag 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.
   
    
6. Switch to Report Design View.
    
7. Move your mouse over the chart until the pointer turns to the Move shape , 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. Switch 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. Double-click the chart.
   The 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. Click on the Help menu.
   Surprise!
   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. Click 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 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. On 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.
   
    
5. Click 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. Switch 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. On 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. Change 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.
   
    
8. Right 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.
    
     
12. Click 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. Switch 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.
   
   Numbering Objects: The default names for objects include a number, starting with zero.
    
4. Click 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 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. Drag 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. Run the query by clicking the Run button .
    The datasheet shows 3 values, one for each agent, and includes their names. Hurrah! Now we have actual names to use.
     
12. Switch 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!
    
     
16. Click 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 , and not the shape for resizing the section .
     
21. Drag 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. Save the report. (Chart-Trip Totals by Agent)
     
24. Add a label containing your name to the Page Header at the left.
     
25. Print.