LEARN MS-ACCESS TIPS AND TRICKS: Graph Charts

Showing posts with label Graph Charts. Show all posts

PIE Chart and live data on Form

a.p.r. pillaiSeptember 28, 2013Graph Charts No comments

Introduction.

Students' Exam Scores in five different Subjects are recorded in Table: tbl_Students. A sample Table image is given below:

The name of the student, from the current record on the Form, is used as criteria in a Query, to filter the exam records of that student, as source data for the PIE Chart on the same Form. The Sample Form- image with the PIE Chart is given below:

When any one of the exam records, out of five subjects of a particular student becomes current on the Form the PIE Chart should display the marks of all five subjects and the percentage of Maximum Total Marks, on the same Form. This is the tricky part; we cannot design a Graph Chart on the Form, while the Form is in normal view to filter the data in the query as the source for the same Chart.

Data Source Queries.

We need three simple queries to filter and prepare the data for the Chart. Besides that, we need a temporary table to store the Total of all Obtainable Maximum Marks (to calculate the percentage of total Obtained marks) on each subject for the chart (100 x 5 = 500). The image of the temporary table is given below:

The Three-Part Approach.

We will divide the task into three parts so that the procedure is easy to understand:

Part-A: Create tbl_Students and tmp_MaxMarks.
Part-B: Design Form frm_Students for tbl_Students and creates three simple Queries to prepare the data for Charts.
Part-C: Create a PIE Chart on frm_Students Form.

Part-A:

Create a Table with the Structure, as shown in the first image on the top, and name the table as tbl_Students. You can ignore the two empty fields on the Table. As you can see the table contains three students’ exam scores for five subjects, out of hundred each.
Key in those sample data into tbl_Students Table.
Create a small table with two fields, with the structure shown in the above image, and save it with the name tbl_MaxMarks.
Create a single record with the sample data, shown in the image above.

Part-B:

Design a Form in Column format, using tbl_Students, leaving enough space on the right side to create the PIE Chart. You can use the Form Wizard to create the Form quickly and save the Form with the name frm_Students.
We will create three Queries before we proceed with some more work on the Form.

Create the following Queries by copying, pasting the SQL String into the new Query’s SQL Editing Window and saving it with the name as indicated:

Query-1: StudentsQ

SELECT [Student] & " " & [Subject] AS [Desc], tbl_Students.Marks
FROM tbl_Students
WHERE (((tbl_Students.Student)=[forms]![frm_Students]![student]));

Query-2: MaxMarksQ

SELECT tmp_MaxMarks.Desc, [MaxMarks]-DSum("Marks","StudentsQ") AS Max_Marks
FROM tmp_MaxMarks;

Query-3: UnionPieChart

SELECT StudentsQ.* FROM StudentsQ
UNION ALL SELECT MaxMarksQ.* FROM MaxMarksQ;

Open frm_Students in Design View.
Create two TextBoxes below the existing fields.
Click on the first Text box and press F4 to display the Property Sheet.
Change the Name Property value to Obtained.
Type the following expression into the Control Source Property:
```
=DSum("Marks","StudentsQ")
```
Change the Caption Property Value, of the Child Label of the Textbox, to Total Obtained:.
Click on the second Textbox to select it.
Write the following expression into the control Source Property:
```
=[Obtained]/DLookUp("MaxMarks","tmp_MaxMarks")
```
Change the Format Property Value to Percentage.
Change Caption Property value of the Child Label of the Text Box to % of Total:.
Save the Form and open it in Normal View.

Let us run a review of what we did so far.

We have created the main table tbl_Students and designed the Form frm_Students, using tbl_Students.

While the frm_Students is active, the StudentsQ filters the data using the current student's name (say John) on the Form as criteria.

Second Query (MaxMarksQ) Sum up the total obtained marks from StudentsQ. Subtracts it from the Maximum Marks of all five subjects (500) from tmp_MaxMarks. The difference between these two values, if any, is used for calculating the percentage of marks the student lost. In other words, if this figure on the PIE chart shows 10%, then the student could only obtain 90% aggregate marks out of 500.

Third Query: UnionPieChart combines the Data of StudentsQ and MaxMarksQ queries and is used as Data Source for the PIE Chart.

Part-C:

Our PIE Chart should be created on the frm_Students, but the Form should be kept open in a normal view to filter the data in StudentsQ for the chart. We can create the Chart only when the form is in design view. I hope you can see now why we need a little trick to pull this off.

Now, the frm_Students Form is in the open state and the exam result data of John (the first student on the Form) is filtered and available in StudentsQ. We should go through the following steps to create the PIE Chart on the frm_Students.

While keeping the frm_Students in Form View, open a new Form in Design View.
Enable Use Control Wizard Button on the Toolbar above and select Insert Chart Tool from the Controls group.
Draw a Chart in a rectangular shape, large enough to show the formatting (like Value Labels, Title, etc.) we are going to do, like the sample image given above.
Select Queries from the View group from the Wizard control and select UnionPieChart from the displayed query list.
Click Next to proceed to the next screen and click on >> button to select all the displayed fields for the chart.
Click on the PIE Chart Icon to select it and click the Next button twice then type the following as a heading on the Title control:
Max. Marks Each=100, 500 Total
Select No, don't display a Legend radio button, then Click Finish.
Double-Click on the PIE Chart to change it into Edit Mode.
Right-click on an empty area of the chart to display the context menu.
Select Chart Options from the displayed menu.
Select the Data labels Tab and put Check Marks in Category Name, Values, and Percentage.
Click OK to close the control.
We will reduce the size of the PIE slice labels to smaller font sizes.
Click on one of the PIE slice Labels. This action will select all the Labels together.
Change the Font size to something like 12 points, using the Font/Size Tool above.

The completed design of the PIE Chart is given below.
Click outside the Chart Object in the Form, to exit from Chart-Edit Mode, but the chart will be still in the selected state.
Right-Click on the chart, to display the shortcut menu, and click Copy to copy the Chart-Object on the Clipboard.
You may save the form with a name and close it if you would like to keep it safe. But, we don’t need it anymore.
Now, Change the frm_Students into Design View.
Click somewhere on the Detail Section of the Form to make the Detail section current.
Right-click on the Detail Section and select Paste from the displayed menu.
Drag the PIE Chart and place it on the right side of the data fields.
Click on the left top corner of the Form and display the Property Sheet (F4) of the Form.
Select the Event Tab of the Property Sheet and click on the On Current property.
Select the Event Procedure from the drop-down list and click on the build (. . .) Button to open the VBA editing window.
Write the following statement in the middle of the Form_Current() Event Procedure:
```
Me.Refresh
```
When completed the procedure will look like the following:
```
Private Sub Form_Current()
     Me.Refresh
End Sub
```
When you move the records on the Form, from one to the other, the Form_Current() event procedure will update the record set with the change on the StudentsQ as well.
Close the VBA editing window.
Save the frm_Students Form and open it in Normal View.

The first five records on the Form belong to student John. If you move one record at a time you will not find any difference on the Chart up to the fifth record, because the values of all the five records are shown on the PIE Chart. The sixth to tenth records belongs to the second student and the 11th record onwards belongs to the third student. You may type 6 or 11 on the Record Navigation Control directly to quickly display other students' marks on PIE Chart.

Missing Lines in Line-Chart

a.p.r. pillaiAugust 14, 2012Graph Charts No comments

Introduction.

You spent several hours preparing the data for your Line-Chart. Designed the Line-Chart on a Report with Title, Data Labels, and Legends, and it looks nice except for one big problem with the Profit/Loss line. The Line doesn’t show up on the graph except for two marker points on Qrtr1 and Qrtr3 value points and nothing shows on Qrtr2 and Qrtr4 value locations.

Check the sample Graph Chart Image shown below with the points marked with yellow color on the Profit/Loss line:

Take a look at the following Graph Chart Image with the Source Table displayed:

Tracking Down the Real Issue.

Did you notice where the actual problem is? In the Profit/Loss row, in Qrtr2 and Qrtr4 cells have Null values in the table resulting in the Graph Chart ignoring these cell values and not connecting other values with lines, without breaks in between. While preparing data (source Table/Query) for the Graph Chart ensure that none of the cells end up with a Null value. If there are Cells with Null values then fill them up with Zeroes.

The corrected Chart Table, filled with zero values in empty cells resulted in connecting the points with the line correctly on the Graph Chart image shown above.

You can modify the Chart Source Value by modifying the Row Source Property SQL value, without directly updating zeroes on the Source Table.

Modifying the Chart Data Source Query.

Open the Report with the Graph Chart in Design View.
Click on the Chart’s outer frame to select it.
Display the Property Sheet.
Click on the build (. . .) button on the Row Source Property to open the Graph Chart Source Query in Design View.

Modify the Query Columns to get the SQL modified as shown below:

SELECT Chart.Desc, Val(nz([qrtr1],0)) AS [Qrtr-1], 
  Val(nz([qrtr2],0)) AS [Qrtr-2],
  Val(nz([qrtr3],0)) AS [Qrtr-3],
  Val(nz([qrtr4],0)) AS [Qrtr-4] FROM Chart;

Save and close the Query.
Open the Report with the Graph Chart in Print Preview mode to view the effect of the change.

PIE CHART OBJECT AND VBA

a.p.r. pillaiJuly 11, 2008Graph Charts, msaccess graphs 4 comments

Introduction.

This is the continuation of a discussion on working with Chart Object in VBA. We have created a small Table and a Report with a Chart Object on it, in the earlier Post Working with Chart Object in VBA and going through a few simple Property changes in the Chart Object to get prepared for the Demo of the VBA Programs. Please go through Steps 1 to 7 explained in the earliest Post, by following the link above, and continue.

You may have a look at the Column Chart and VBA topic at your convenience as well.

PIE Chart Options.

If you have already tried out the Code given in the earlier Posts then you are familiar with most of the Code Segments that you see here and will have no difficulty understanding them. The difference is only in the formatting method of individual PIE Slices and a few property changes also. In this example, we will try three types of Pie Charts: 1. 3D Pie, 2. 3D Pie Exploded, 3. Pie of Pie.

Copy and Paste the code given below into a Global Module in your Database.

Public Function PieChart(ByVal ReportName As String, ByVal ChartObjectName As String)
'---------------------------------------------------
'Author : a.p.r. pillai
'URL    : http://www.msaccesstips.com
'Date   : July-2008
'Customized Source Code : from Microsoft Access Help
'---------------------------------------------------
Dim Rpt As Report, grphChart As Object
Dim msg As String, lngType As Long, cr As String
Dim ctype As String, typ As Integer, j As Integer
Dim db As Database, rst As Recordset, recSource As String
Dim colmCount As Integer, chartType(1 To 5) As String
Const twips As Long = 1440

On Error GoTo PieChartChart_Err

chartType(1) = "3D Pie Chart"
chartType(2) = "3D Pie Exploded"
chartType(3) = "Pie of Pie"
chartType(4) = "Quit"
chartType(5) = "Select 1-4, 5 to Cancel"

cr = vbCr & vbCr
msg = ""
For j = 1 To 5
  msg = msg & j & ". " & chartType(j) & cr
Next

ctype = "": typ = 0
Do While typ < 1 Or typ > 4
 ctype = InputBox(msg, "Select Chart Type")
 If Len(ctype) = 0 Then
    typ = 0
 Else
    typ = Val(ctype)
 End If
Loop

Select Case typ
    Case 4
        Exit Function
    Case 1
       lngType = xl3DPie
    Case 2
       lngType = xl3DPieExploded
    Case 3
       lngType = xlPieOfPie
End Select

DoCmd.OpenReport ReportName, acViewDesign
Set Rpt = Reports(ReportName)

Set grphChart = Rpt(ChartObjectName)

grphChart.RowSourceType = "Table/Query"
recSource = grphChart.RowSource

If Len(recSource) = 0 Then
   MsgBox "RowSource value not set, aborted."
   Exit Function
End If

'get number of columns in chart table/Query
'if Table or SQL string is not valid then
'generate error and exit program
Set db = CurrentDb
Set rst = db.OpenRecordset(recSource)
colmCount = rst.Fields.Count
rst.Close

're-size the Chart
With grphChart
    .ColumnCount = colmCount
    .SizeMode = 3
    .Left = 0.2917 * twips
    .Top = 0.2708 * twips
    .Width = 5 * twips
    .Height = 4 * twips
End With

'activate the chart for modification.
grphChart.Activate

'Chart type, Title, Legend, Datalabels,Data Table
With grphChart
     .chartType = lngType
    .HasLegend = True
    .HasTitle = True
    .ChartTitle.Font.Name = "Verdana"
    .ChartTitle.Font.Size = 14
    .ChartTitle.Text = chartType(typ) & " Chart."
    .HasDataTable = False
End With

'format Pie slices with gradient color
With grphChart.SeriesCollection(1)
    .HasDataLabels = True
    .DataLabels.Position = xlLabelPositionBestFit
    .HasLeaderLines = True
    .Border.ColorIndex = 19 'edges of pie shows in white color
    For j = 1 To .Points.Count
        With .Points(j)
            .Fill.ForeColor.SchemeColor = Int(Rnd(Timer()) * 54) + 2
            .Fill.OneColorGradient msoGradientVertical, 4, 0.231372549019608
            .DataLabel.Font.Name = "Arial"
            .DataLabel.Font.Size = 10
            .DataLabel.ShowLegendKey = False
            '.ApplyDataLabels xlDataLabelsShowValue
            .ApplyDataLabels xlDataLabelsShowLabelAndPercent
        End With
    Next
End With

'Chart Area Border
With grphChart
    .ChartArea.Border.LineStyle = xlDash
    .PlotArea.Border.LineStyle = xlDot
    .Legend.Font.Size = 10
End With

'Chart Area Fill with Gradient Color
With grphChart.ChartArea.Fill
    .Visible = True
    .ForeColor.SchemeColor = 17
    .BackColor.SchemeColor = 2
    .TwoColorGradient msoGradientHorizontal, 2
End With

'Plot Area fill with Gradient Color
With grphChart.PlotArea.Fill
    .Visible = True
    .ForeColor.SchemeColor = 6
    .BackColor.SchemeColor = 19
    .TwoColorGradient msoGradientHorizontal, 1
End With

grphChart.Deselect

DoCmd.Close acReport, ReportName, acSaveYes
DoCmd.OpenReport ReportName, acViewPreview

PieChart_Exit:
Exit Function

PieChart_Err:
MsgBox Err.Description, , "PieChart()"
Resume PieChart_Exit
End Function

Press Ctrl+G to display the Debug Window (Immediate Window) in the VBA Editing View, type the following command in the Debug Window, and press Enter:

PieChart "MyReport1", "Chart1"

Sample run: an image of Option-1:

Sample run: an image of Option-2:

Sample run: an image of Option-3:

Run it from the Command Button Click.

If you prefer to run this command from the On_Click() Event Procedure of a Command Button on your Form, you may do so. The Program will open myReport1 in Design View, resize the Chart1 Chart Object, big enough for Printing or Viewing, and format the Chart elements. The Program saves the Report and re-opens it in Print Preview.

The Analysis of Values appearing in a PIE Chart is done differently than in other forms of Charts. That is why PIE Charts take only one set of Values.

If, four of us join together and decided to buy a Cake worth $100 and each one of us takes a share of the Cost of $6 (I will take the first share amount if no icing), $60, $20 and $14, the claim on the Cake is in relation to the share of Amount. The Pie Slices will show the size of each one of our shares.

In the earlier examples, the Formatting of all the Bars of a particular Data Series is applied together as a single collection of Objects with higher-level reference grphChart.SeriesCollection(). But in Pie Chart, individual elements of a Data Series (Pie Slices) are referenced by getting one step further deep into the collection of Objects as grphChart.SeriesCollection().Points() and formats them separately with different sets of Gradient Colors.

Tips: If you would like to format individual Bars of a Bar Chart you can use this method. But you must remove the following Properties from the Code, used for PIE Chart:

.DataLabels.Position = xlLabelPositionBestFit
.HasLeaderLines = True
.Border.ColorIndex = 19 'showing edges of Pie slices with white color
.ApplyDataLabels xlDataLabelsShowLabelAndPercent

Chart Formatting.

The X-Axis and Y-Axis Titles are not used in Pie Charts and these Code segments are removed from the Program. Chart Area and Plot Area are formatted with a different set of light Colors, different from our earlier examples. With these elements also we can use the Rnd() Function to generate different sets of values in each Run, formatting with Gradient Color. But, it may not come up with the correct color combination every time, good enough for viewing or Printing. If you do not like the current Scheme of Color Values you may try out different Color Values from the Color Chart given on the first Post Working with Chart Object in VBA.

Download the Demo Database.

Download Demo Database

Column Chart and VBA

a.p.r. pillaiJuly 04, 2008Graph Charts, msaccess graphs 1 comment

Introduction.

In our earlier example, Working with Access Graph Chart in VBA, we have made preparations to try out MS Access Charts with VBA and did sample Runs for three Types of Charts viz. Column Clustered, Line Chart, and Pie Chart. If you have landed on this page for the first time you may go to the earlier post by clicking on the link above. Go through the simple preparations explained there, by following Steps 2 to 7, to try out the code given here.

Earlier, we could change the Chart type through Code to one of three types with common property settings that can be applied to all three. But we have conditionally skipped some properties not relevant to PIE Chart, like Chart Axis Titles. We will deal with the PIE Chart separately later and will see how to format individual Pie Slices and change other settings as well.

Here, we will try only one Category of Charts viz. Column Chart Type and will run the Code for 4 different designs. This type of Chart with vertical Bars is the most commonly used Chart and is loosely referred to as a bar chart. The Category Description of the Values plotted on the chart are placed horizontally along the X-Axis, below the chart and the Chart Scale is calculated automatically and placed vertically on the left side of the Chart (on the Primary Y-Axis) with fixed intervals of values. This can be set manually too if needed.

When very small Values are plotted along with large values on the Chart the variations of the small values over a period of time may not be clearly visible and may become difficult to differentiate one value from the other. In such situations, the small value Series can be plotted on the Secondary Y-Axis (right-side vertical axis) and the variations in the values will be clearly visible. With the help of data labels, the values on the chart become more meaningful and easily analyzable.

When 3D Chart Type is selected Y-Axis gives way to Z-Axis for displaying Tick Labels and Axis Titles. The Charts with Horizontal Bars are the real Bar Charts.

Sample Demo Run Code

We have created options in the Code to run for the following Options:

Column Clustered.
Column Clustered (Reverse Plot Order - flipped upside down)
3D Column Clustered.
3D Column Stacked.

There are optional properties in this Code that you can use to change the shape of the Bars to Cone or Cylinder.

In this example, we are turning On the HasDatatable Property of Chart Object that will display the Source Data Table on the Chart. The X-Axis Category Labels Qrtrl to Qrtr4 becomes part of the Data Table display and if we attempt to use the X-Axis Tick-Label Property to set Font or Font Size we will run into errors. We have used it conditionally without eliminating the code altogether so that you will know the difference.

DataLabels Orientation Property gives us the ability to display the Data Labels tilted to a specified Angle in Degrees, besides normal Horizontal or Vertical formats. To try out the examples do the following:

The VBA Code

Copy and Paste the following code into a Global Module of your Database and save it. If you have already gone through the earlier Post and gone through Step-2 to 7 then you are ready to run the Code.

Public Function ColumnChart(ByVal ReportName As String, ByVal ChartObjectName As String)
'---------------------------------------------------
'Author : a.p.r. pillai
'Date   : June-2008
'URL    : http://www.msaccesstips.com
'Customized Source Code : from Microsoft Access Help
'---------------------------------------------------
Dim Rpt As Report, grphChart As Object
Dim msg As String, lngType As Long, cr As String
Dim ctype As String, typ As Integer, j As Integer
Dim db As Database, rst As Recordset, recSource As String
Dim colmCount As Integer, chartType(1 To 6) As String
Const twips As Long = 1440

On Error GoTo ColumnChart_Err

chartType(1) = "Clustered Column"
chartType(2) = "Reverse Plot Order"
chartType(3) = "3D Clustered Column"
chartType(4) = "3D Stacked Column"
chartType(5) = "Quit"
chartType(6) = "Select 1-4, 5 to Cancel"

cr = vbCr & vbCr
 msg = ""
For j = 1 To 6
  msg = msg & j & ". " & chartType(j) & cr
Next

ctype = "": typ = 0
Do While typ < 1 Or typ > 4
 ctype = InputBox(msg, "Select Chart Type")
 If Len(ctype) = 0 Then
    typ = 0
 Else
   typ = Val(ctype)
 End If
Loop

Select Case typ
    Case 5
        Exit Function
    Case 1,2
       lngType = xlColumnClustered
    Case 3
       lngType = xl3DColumnClustered
    Case 4
       lngType = xl3DColumnStacked
End Select

DoCmd.OpenReport ReportName, acViewDesign
Set Rpt = Reports(ReportName)

Set grphChart = Rpt(ChartObjectName)

grphChart.RowSourceType = "Table/Query"
recSource = grphChart.RowSource

If Len(recSource) = 0 Then
   MsgBox "RowSource value not set, aborted."
   Exit Function
End If

'get number of columns in chart table/Query
Set db = CurrentDb
Set rst = db.OpenRecordset(recSource)
colmCount = rst.Fields.Count
rst.Close

With grphChart
    .ColumnCount = colmCount
    .SizeMode = 3
    .Left = 0.2917 * twips
    .Top = 0.2708 * twips
    .Width = 5 * twips
    .Height = 4 * twips
End With

grphChart.Activate

'Chart type, Title, Legend, Datalabels,Data Table
With grphChart
     .chartType = lngType
     If typ = 3 Or typ = 4 Then 
' for 3D Charts only
       .RightAngleAxes = True
       .AutoScaling = True
     End If
    .HasLegend = True
    .HasTitle = True
    .ChartTitle.Font.Name = "Verdana"
    .ChartTitle.Font.Size = 14
    .ChartTitle.Text = chartType(typ) & " Chart."
    .HasDataTable = True
    .ApplyDataLabels xlDataLabelsShowValue
End With

'apply gradient color to Chart Series    
For j = 1 To grphChart.SeriesCollection.Count
      With grphChart.SeriesCollection(j)
        '.Interior.Color = RGB(Int(Rnd(j) * 200), Int(Rnd(j) * 150), Int(Rnd(j) * 175))
        .Fill.OneColorGradient msoGradientVertical, 4, 0.231372549019608
        .Fill.Visible = True
        .Fill.ForeColor.SchemeColor = Int(Rnd(Timer()) * 54) + 2
        '.Barshape = xlCylinder 
' xlCylinder, xlConeToPoint, xlBox, xlPiramidtoMax
        If typ = 1 Then
            .Interior.Color = msoGradientVertical
        End If
        .DataLabels.Font.Size = 10
        .DataLabels.Font.Color = 3
        If typ = 1 Then
            .DataLabels.Orientation = xlUpward
        Else
            '.DataLabels.Orientation = xlHorizontal
            .DataLabels.Orientation = 45 'titlted angle in degrees
        End If
        End With
    Next

'Y-Axis /(Z-Axis for 3D)Title
With grphChart.Axes(xlValue)
   If typ = 2 Then
    .ReversePlotOrder = True
 'flips upside down
   Else
    .ReversePlotOrder = False
   End If
    .HasTitle = True
    .HasMajorGridlines = True
    With .AxisTitle
        .Caption = "Values in '000s"
        .Font.Name = "Verdana"
        .Font.Size = 12
        .Orientation = xlUpward
    End With
End With

'X-Axis Title
With grphChart.Axes(xlCategory)
    .HasTitle = True
    .HasMajorGridlines = True
    .MajorGridlines.Border.Color = RGB(0, 0, 255)
    .MajorGridlines.Border.LineStyle = xlDash
    With .AxisTitle
        .Caption = "Quarterly"
        .Font.Name = "Verdana"
        .Font.Size = 10
        .Font.Bold = True
        .Orientation = xlHorizontal
    End With
End With

'Primary Y/Z Axis values label's font size
With grphChart.Axes(xlValue, xlPrimary)
     .TickLabels.Font.Size = 10
End With

'X-Axis category Labels (Qrtr1, Qrtr2...)
If grphChart.HasDataTable = False Then
    With grphChart.Axes(xlCategory)
        .TickLabels.Font.Size = 8
    End With
Else
    grphChart.DataTable.Font.Size = 9
End If

'Chart Area Border
With grphChart
    .ChartArea.Border.LineStyle = xlDash
    .PlotArea.Border.LineStyle = xlDot
    .Legend.Font.Size = 10
End With

'Chart Area Fill with Gradient Color
With grphChart.ChartArea.Fill
    .Visible = True
    .ForeColor.SchemeColor = 2
    .BackColor.SchemeColor = 19
    .TwoColorGradient msoGradientHorizontal, 2
End With

'Plot Area fill with Gradient Color
With grphChart.PlotArea.Fill
    .Visible = True
    .ForeColor.SchemeColor = 2
    .BackColor.SchemeColor = 42
    .TwoColorGradient msoGradientHorizontal, 1
End With

grphChart.Deselect

DoCmd.Close acReport, ReportName, acSaveYes
DoCmd.OpenReport ReportName, acViewPreview

ColumnChart_Exit:
Exit Function

ColumnChart_Err:
MsgBox Err.Description, , "ColumnChart()"
Resume ColumnChart_Exit
End Function

Preparing for Demo Run

Insert a new MS-Office Graph Chart Object on a new Report, change the basic property values as given in the earlier Article (Working with Graph Chart in VBA) and save the Report with the name myReport2. We can use the same Table that we have created for earlier examples.
NB: Always create a new Chart Object for a new set of examples rather than using the same Chart created earlier. Some property changes were found shown incorrectly when re-used for different Chart Types.
The Code can be tested either by running from a Command Button Click Event Procedure or directly from the Debug Window (Immediate Window). Press Alt+F11 to open VBA Window, press Ctrl+G for Debug Window and type the following command and press Enter Key.
```
ColumnChart "myReport2", "Chart1"
```
A menu will appear with four Column Chart Options. Type 1 in the Text Box and press the Enter key to run the first option.
The Program Opens the Report myReport2 in Design View, modifies the Chart Properties, saves the Report with the Chart, and then re-opens it in Print Preview. A sample run image is given below.

Run the program again and this time select Option number 2.

In this run, we are setting the ReversePlotOrder property value to True to Flip the Chart upside down. The sample image is given below.

Every time when you run the Code, the Colors are selected in Random order from the Color Chart given in the earlier Post (Working with Graph Chart in VBA) and will display the Chart Bars differently.

Saving the Report with the Chart.

If you plan to save the Chart and don't like the Color combination currently displayed you may run the option more than once till you get the color combination to your liking. If you want to save the current run of the Chart you may make a copy of the Report or Export the Report into MS-Access Snapshot Viewer Format (MS-Access 2000 or later).

Chart Area and Plot Area are set with light Gradient Color so that they will look better when printed.

Sample Run of Option 3.

Sample Run of Option 4.

In Option 3 and 4 Runs you can see that the Plot Area of the Chart is extended to display the Data Table and X-Axis Title. But, in the first two runs, these are appearing outside the Plot Area.

Saving PowerPoint Presentations.

You can copy and paste the finished Chart into PowerPoint Presentations. Before transporting the Chart to the PowerPoint page, copy and paste the Values from the Table into the Data Grid of the Chart so that you can edit the values in PowerPoint itself if needed.

Double-Click on the Chart Object in Report Design View to activate the Chart and to display the Data Grid.
Open the Table in Datasheet-View, and click on the left top corner of the Table to highlight all the records.
Select Copy from Edit Menu.
Right-Click on the left-top corner cell of the Data Grid and select Paste from the displayed Shortcut Menu.

Download the Demo Database.

Download Demo Database

Working with Chart Object in VBA

a.p.r. pillaiJune 27, 2008Graph Charts, msaccess graphs 10 comments

Introduction.

Working with Microsoft Access Chart Object in VBA is a complicated matter, but interesting too. Once the code is developed it can be used in other Access Databases to prepare Access Graph Charts with the same look and feel in minutes.

There are several Chart Types, Chart Areas, and numerous Object Groups and Objects to work with. The Range of values that can go into them is too many. There are Access Chart Type-specific properties and value ranges, like the Auto Scaling property that can be used only for 3D Charts.

Note: But first, please ensure that you have attached the following important Reference Library Files to your Database, in order to work with VBA Code without running into Errors.:

Attaching System Reference Library Files

Display the Visual Basic Editor window, by selecting the Visual Basic Editor Option from Tools Menu or Code from View Menu.

Select the References from Tools Menu. Put the check mark on the following Library Files in the Available References Dialogue Control:

Microsoft Office 12.0 Object Library
Microsoft Excel 12.0 Object Library

These are also necessary when you run Options in the Demo Database, attached for Downloading at the end of this Page.

I did some searching, and experimenting, and created a VBA Routine that can modify the Access Graph Chart Elements with beautiful formatting, to my liking. I hope you will like it too. The sample run result image is given below.

Formatting Chart Elements

The Resizing of the Graph Chart and formatting of several elements, like Chart Title, Chart Axis Titles, Chart Area, Chart PlotArea, and Chart Bars with Gradient color, Legends, Grid Lines, Data Labels, and alignment of Data Labels are done with VBA Code. Placing a Chart Object on the Report with few property changes, as a starter kit, is done manually.

When the Program is run, it gives three options to choose from:

Bar Chart
Line Chart
Pie-chart.

The same Graph Chart is transformed into any one of these Types and the sample values from a table are used for all three. Pie-Chart will only use the first record from the Table and the treatment of values in the pie-chart is different from Bar Chart or Line Chart. Here, it is used only for example purposes, and Pie-chart is not treated separately.

Color Numbers

The following Color index numbers can be used in different combinations in the Program to get different shades of gradients.

Preparing for Trial Run

Follow the few simple steps explained below to get prepared for the trial run.

Create a Table with the following Structure and save it with the name Table1.
Add the sample records with the values shown above.
Open a new Report in Design View.
Select Object from Insert Menu and select Microsoft Graph 2000 Chart (or Microsoft Graph Chart in MS-Access 2003) from the Object Type List.
The inserted chart object with default values will be in an activated state, displaying the default values in the Data Grid.
Click outside the chart on the Detail Section of the Report to de-activate and de-select the Chart.
Click on the Chart again to select it and display the Property Sheet (View - - >Properties)
Change the following Property Values as given below:
- Name: Chart1
- Row Source Type: Table/Query
- Row Source: Table1
- Column Heads: Yes
Even though the following properties can be changed manually, these are changed through the Code:
- SizeMode : 3 (Zoom)
- Column Count: Number of Columns in the Source Data
- Left: 0.2917?
- Top: 0.2708?
- Width: 5.5729?
- Height: 4.3854?
2D Chart Area Zones
An MS Access 2D Chart Object has 3 different areas:
1. the outer Frame
2. Chart Area where Chart Title, Axis Titles, and Legend Values appear.
3. Plot Area where the chart Data Series Values, Data Labels, Gridlines, Scale, and Category Values appear in the Bar or Line Charts.
If the Size Mode property is set to Clip (value 0) then resizing the Chart with the last four values (Left, Top, Width & Height given above) will only change the size of the Outer Frame. The inner Sections' Chart Area and Plot Area will not resize with the change. If it is set to Stretch or Zoom(Values 1 or 3 respectively) then they will resize to fit within the outer frame area. Zoom is a better setting because it will maintain the Chart in the correct aspect ratio when re-sized, without distortion.
The Column count property value is found from the Row Source Table or Query String.
We are validating the settings in the Row Source Type property. If it is not set with the value Table/Query then it will change to that value. But, if the Row Source property is not set with a Table Name or valid SQL String then the program will terminate with a message.
Save the Report with the Name myReport1.

The VBA Code.

Copy and Paste the following VBA Code into the Global Module and save the Module.

Public Function ChartObject(ByVal ReportName As String, ByVal ChartObjectName As String)
'------------------------------------------------
'Author : a.p.r. pillai
'Date   : June-2008/Revision on 12/2019
'Source Code : from Microsoft Access Help
'and modified certain parameters
'------------------------------------------------
Dim Rpt As Report, grphChart As Object
Dim msg As String, lngType As Long, cr As String
Dim ctype As String, typ As Integer, j As Integer
Dim db As Database, rst As Recordset, recSource As String
Dim colmCount As Integer
Const twips As Long = 1440

'On Error GoTo ChartObject_Err

cr = vbCr & vbCr

msg = "1. Bar Chart" & cr
msg = msg & "2. Line Chart" & cr
msg = msg & "3. Pie Chart" & cr
msg = msg & "4. Quit" & cr
msg = msg & "Select Type 1,2 or 3"

ctype = "": typ = 0
Do While typ < 1 Or typ > 4
 ctype = InputBox(msg, "Select Chart Type")
 If Len(ctype) = 0 Then
    typ = 0
 Else
 typ = Val(ctype)
 End If
Loop

Select Case typ
    Case 4
        Exit Function
    Case 1
       lngType = xlColumnClustered
    Case 2
       lngType = xlLine
    Case 3
       lngType = xl3DPie
End Select

DoCmd.OpenReport ReportName, acViewDesign
Set Rpt = Reports(ReportName)

Set grphChart = Rpt(ChartObjectName)

grphChart.RowSourceType = "Table/Query"

recSource = grphChart.RowSource

If Len(recSource) = 0 Then
   MsgBox "RowSource value not set."
   Exit Function
End If

'get number of columns in chart table/Query
Set db = CurrentDb
Set rst = db.OpenRecordset(recSource)
colmCount = rst.Fields.Count
rst.Close

With grphChart
    .ColumnCount = colmCount
    .SizeMode = 3
    .Left = 0.2917 * twips
    .Top = 0.2708 * twips
    .Width = 5.5729 * twips
    .Height = 4.3854 * twips
End With

grphChart.Activate

'Chart type, Title, Legend, Datalabels,Data Table
With grphChart
     .chartType = lngType
    ' .chartType = xlColumnClustered
    '.AutoScaling = True
    ' only for 3D type
    .HasLegend = True
    .HasTitle = True
    .ChartTitle.Font.Name = "Verdana"
    .ChartTitle.Font.Size = 14
    .ChartTitle.Text = "Revenue Performance - Year 2007"
    .HasDataTable = False
    .ApplyDataLabels xlDataLabelsShowValue
End With

'apply gradient color to Chart Series
If typ = 1 Or typ = 2 Then
    For j = 1 To grphChart.SeriesCollection.Count
      With grphChart.SeriesCollection(j)
        '.Interior.Color = RGB(Int(Rnd(j) * 200), Int(Rnd(j) * 150), Int(Rnd(j) * 175))
        .Fill.OneColorGradient msoGradientVertical, 4, 0.231372549019608
        .Fill.Visible = True
        .Fill.ForeColor.SchemeColor = Int(Rnd(1) * 54) + 2
        If typ = 1 Then
           .Interior.Color = msoGradientVertical
        End If
        .DataLabels.Font.Size = 10
        .DataLabels.Font.Color = 3
        If typ = 1 Then
            .DataLabels.Orientation = xlUpward
       Else
            .DataLabels.Orientation = xlHorizontal
        End If
     End With
    Next j
End If

If ctype = 3 Then
 GoTo nextstep 'skip axes for pie chart
End If

'Y-Axis Title
With grphChart.Axes(xlValue)
    .HasTitle = True
    .HasMajorGridlines = True
    With .AxisTitle
        .Caption = "Values in '000s"
        .Font.Name = "Verdana"
        .Font.Size = 12
        .Orientation = xlUpward
    End With
End With

'X-Axis Title
With grphChart.Axes(xlCategory)
    .HasTitle = True
    .HasMajorGridlines = True
    .MajorGridlines.Border.Color = RGB(0, 0, 255)
    .MajorGridlines.Border.LineStyle = xlDash
    With .AxisTitle
        .Caption = "Quarterly"
        .Font.Name = "Verdana"
        .Font.Size = 10
        .Font.Bold = True
        .Orientation = xlHorizontal
    End With
End With

With grphChart.Axes(xlValue, xlPrimary)
     .TickLabels.Font.Size = 10
End With
With grphChart.Axes(xlCategory)
     .TickLabels.Font.Size = 10
End With

nextstep:

With grphChart
    .ChartArea.Border.LineStyle = xlDash
    .PlotArea.Border.LineStyle = xlDot
    .Legend.Font.Size = 10
End With

'Chart Area Fill with Gradient Color
With grphChart.ChartArea.Fill
    .Visible = True
    .ForeColor.SchemeColor = 2
    .BackColor.SchemeColor = 19
    .TwoColorGradient msoGradientHorizontal, 2
End With

'Plot Area fill with Gradient Color
With grphChart.PlotArea.Fill
    .Visible = True
    .ForeColor.SchemeColor = 2
    .BackColor.SchemeColor = 10
    .TwoColorGradient msoGradientHorizontal, 1
End With

grphChart.Deselect

DoCmd.Close acReport, ReportName, acSaveYes
DoCmd.OpenReport ReportName, acViewPreview

ChartObject_Exit:
Exit Function

ChartObject_Err:
MsgBox Err.Description, , "ChartObject()"
Resume ChartObject_Exit
End Function

Running the Code

Open the VBA Module that you have pasted the code if you have closed it.
Run the code from the Debug Window (press Ctrl+G to display the Debug Window) using the following syntax:
ChartObject "myReport1", "Chart1"
Or call the function from On_Click() Event Procedure of a Command Button on a Form.
Select Chart Type 1, 2, or 3 for Bar Chart, Line Chart, or Pie respectively.

The Program will open the Report myReport1 in Design View, modifies the Graph Chart for the selected Type, Saves it, and then Re-opens it in Print Preview. You may minimize the VBA Window (Alt+F11) to view the Chart. Since the Report is saved after the changes, you may open it manually in the design view or Print Preview.

When you run the Code for Bar Chart or Line Chart, the Gradient Scheme Color Codes are selected Randomly, which will give different shades of Gradient Colors every time.

NB: Any suggestions for improvement are welcome.

Download the Demo Database.

Download Demo Database

MS-Access and Graph Charts2

a.p.r. pillaiSeptember 05, 2007Graph Charts, msaccess graphs 2 comments

Continuation of Last Week's discussion

If you have landed straight on this page, please go through the earlier Post MS-Access & Graph Charts and then continue.

Sample Data for Pie-Chart
Desc	Veh Sales	Parts Sales	Service Sales
Total Sales	450000	645000	25000

Create a Table with the above structure and data, and save it with the name pie_Table.
Open a new Report in the design view. Select the Object option from the Insert Menu, select Microsoft Graph-Chart, and then click OK. A Chart Object with default values is inserted into the Report.
Click outside the chart on the report to deselect the chart and to come out of Edit Mode. Click again on the chart to select it, display the property sheet, and change the following values:
- Size Mode = Zoom
- Row Source Type = Table/Query
- Row Source = Pie_Table
- Column Heads = Yes
- Left = 0.3"
- Top = 0.3"
- Width = 6.0"
- Height = 4.0"
Creating and Formatting a 3-D Pie Chart.
1. Double-click on the chart control in your report or form to open the Chart Formatting Toolbar.
2. From the Chart Type Toolbar, select 3-D Pie Chart.
  Alternatively:
  - Right-click on a blank area inside the chart (not on the pie itself).
  - Choose Chart Type from the shortcut menu.
  - Select 3-D Pie Chart and click OK.
3. Open the Pie_Table:
  - Click on the top-left corner of the datasheet (grid) to select all data.
  - Select Edit > Copy from the menu.
4. Go back to your chart:
  - Click the top-left corner of the chart's datasheet grid.
  - Select Edit > Paste to paste the data.
5. Remove extra sample data:
  - If any extra rows or columns remain after pasting, select them and use Edit > Cut to delete.
6. Resize the Pie:
  - Click on the shaded area surrounding the pie to select it.
  - Drag the bottom-right sizing handle outward to enlarge the pie slightly.
7. Format the Plot Area:
  - Right-click on the shaded area around the pie.
  - Select Format Plot Area from the shortcut menu.
  - Set Area Options to None.
  - Set Border Options to None, then click OK.
8. Set Chart Title and Data Labels:
  - Right-click on an empty area of the chart.
  - Select Chart Options.
  - On the Titles tab, enter "Total Revenue" in the Chart Title box.
  - Switch to the Data Labels tab, check the Percentage option under "Label Contains".
  - Click OK to apply your changes.

Click to Enlarge

A chart with more than one set of Bars.

**Table1**
Desc	Qtr1	Qtr2	Qtr3	Qtr4
A_Revenue	25000	35000	20000	40000
B_Expenses	15000	20000	13000	17000
C_Income	10000	15000	7000	23000

Create a table using the field structure and data provided above, and save it as Table1. Then, follow the same steps outlined in the earlier post, MS-Access and Graph Charts, starting from Step 4, to create the chart shown below. In Step 5, set the Row Source property to Table1.

The completed Bar Chart, created using the sample data above, illustrates the quarterly performance of individual areas—Revenue, Expenses, and Income—and is shown below.

Click to Enlarge

Customizing Chart Y-axis Scale Values

The Y-axis scale of the chart, along with the major unit intervals (e.g., 0, 5000, 10000), is automatically calculated and displayed by MS Access. However, when the chart data contains smaller values, it may be necessary to adjust these intervals for better visibility. In such cases, you can manually customize the Y-axis scale to use smaller unit intervals as needed.

To adjust the Y-axis scale manually, double-click on the chart to enter edit mode. Then, right-click on the Y-axis (the vertical line displaying the scale values) and select Format Axis from the shortcut menu. In the dialog box that appears, go to the Scale tab and modify the values as needed. For example, you can change the Minimum, Maximum, or Major Unit settings to better suit your chart data.

Minimum = 0
Maximum = 51000
Major Unit = 3000

Click to Enlarge

Leave the other values unchanged. Click OK to update the new scale settings on the Chart.

Once you manually change the Y-axis scale settings, they will remain fixed, even if the actual chart values exceed the defined maximum. In such cases, you must update the maximum value manually to ensure all data points are displayed correctly. Alternatively, you can enable automatic scaling by checking all relevant options in the Scale tab, allowing MS Access to recalculate and adjust the scale values dynamically as the data changes.

Formatting Data Labels.

You can adjust the alignment of the chart’s data labels to improve readability. Right-click on any label and choose Format Data Labels from the shortcut menu. Go to the Alignment tab and select one of the diamond-shaped icons under Orientation to change the label direction. Feel free to experiment with the other alignment options to find the best fit for your chart layout.

You can display the actual data table used to generate the chart alongside the chart itself. To do this, double-click on the chart to enter edit mode. Then, right-click on an empty area outside the plot area and select Chart Options. Navigate to the Data Table tab and check the Show Data Table option.

Click to Enlarge

Secondary Y-axis Usage.

Sometimes, we need to display smaller values alongside much larger ones on the same chart. For example, if the income values for all four quarters are below 3000, their bars or lines (in a line chart) may appear too small, making it difficult to compare them effectively.

In such scenarios, using a Secondary Y-axis allows you to scale smaller values independently, enhancing the visibility of bars or lines representing those values. Including data labels further improves clarity. The sample image below illustrates this, with Income values (colored bars) plotted on the secondary Y-axis.

Click to Enlarge

Adjusting Bar Width.

To reduce the thickness of the blue bars and make them as narrow as the other bars, you need to increase the gap between them. Double-click the chart to enter edit mode, then right-click one of the blue bars and select Format Data Series. On the Options tab, set the Gap Width value to 340, and click OK to apply the changes to the chart.

The Image of a Chart plotted with the same values in Custom Chart Type Tubes is given below:

Click to Enlarge

MS-Access and Graph Charts

a.p.r. pillaiAugust 29, 2007Graph Charts, msaccess graphs 11 comments

Access Reports and Graph Charts.

Access Reports are a powerful tool for presenting information in both numbers and text. However, Graph Charts take it a step further by conveying data visually, often revealing insights at a glance. With a basic understanding of how charts work, you can easily create them in Access. The key principle is to illustrate the progression or change of an event over time using dots and lines that reflect corresponding values, instead of relying solely on raw numbers or text.

Before creating graphs in MS Access, it's essential to understand how to structure your data appropriately. To get familiar with this process, we'll explore a few examples that use a single set of values and demonstrate the required data format for generating meaningful graphs.

Events such as daily temperature changes, fluctuations in a patient's body temperature (for health monitoring in hospitals), or variations in gold prices over time can all be effectively visualized using line or bar charts. Traditionally, these are plotted manually on graph paper by marking each data point according to a fixed scale, starting from zero at the bottom of the Y-axis (vertical axis), which is calibrated based on the highest expected value.

Each day's data point is plotted as a dot along the Y-axis, aligned with the corresponding date on the X-axis (horizontal axis). These dots can be connected with lines to show trends, and the actual values can be labeled near each dot. A legend is typically included to describe what the graph represents (e.g., Temperature).

Instead of using paper and pencil, we can achieve the same results—more efficiently and professionally—using the Microsoft Graph Chart object in MS Access.

Preparing for a simple Chart.

We plan to monitor the daily Stock Index, and the values are recorded in an Access Table as given below.

Desc	Date	Val
Stock Index	01-08-07	1245
Stock Index	02-08-07	1455
Stock Index	03-08-07	1395
Stock Index	04-08-07	1575
Stock Index	05-08-07	1125

Desc	01-08-07	02-08-07	03-08-07	04-08-07	05-08-07
Stock Index	1245	1455	1395	1575	1125

Click to Enlarge

When the above data is plotted in the form of Charts, the contour of the Charts shows the trend of the stock index at a glance rather than reading the numbers and comparing them.

Before preparing the data for the Chart, we must decide what Type of Chart is best suited for the data.

For example, if we want to present a chart showing the company's total revenue from various sources—such as Vehicle Sales, Parts Sales, and Service Sales—and highlight each category's contribution to the overall total, a Pie Chart would be the ideal choice.

However, if we want to display the monthly performance of each category separately over time, a Line Chart or Bar Chart would be more appropriate, as they are better suited for showing trends and comparisons across periods.

If the source data for a chart changes at regular intervals—such as every month—then the underlying Table or Query should be designed to automatically reflect the updated data. This ensures that the chart dynamically displays the latest information without requiring manual modifications each time the data changes.

Charts in Excel and MS-Access.

Creating charts in Access isn't as straightforward as it is in Excel. In this section, we’ll explore the most commonly used chart types in Access: Line, Bar, and Pie charts. If you're familiar with Excel charts, you might initially find Access’s data preparation methods a bit challenging. However, once you get past the learning curve, you will find Access charts just as powerful—and might not feel the need to return to Excel at all.

However, unlike Excel, Access offers the advantage of using various types of Queries and Macros for automating the chart data preparation process. This allows us to extract and present key figures from large volumes of data quickly and efficiently, regardless of the chart type. Once the chart setup is complete, it can run consistently without requiring manual intervention.

Note: You can enhance charting capabilities in Access by attaching the Microsoft Excel Object Library to the Access database's list of References. This enables the use of Excel’s rich variety of chart types and advanced formatting options directly within Access.

Line and Bar Charts.

The Sample Source Data presented in the first format above must be changed into the 2nd format to create both Line and Bar Charts. In Excel, both formats are valid. With the help of a Cross-Tab Query, we can change the data format for our Chart.

Create a Table with the Field Names Desc, Date, and Val, and save it with the name Stock1.
Key in the six records from the sample data above, as we normally maintain data in Access Tables.
Display the SQL Window of a new Query (don't select a table or Query from the list displayed), copy and paste the SQL String of a Cross-Tab Query given below into the SQL Window, and save it with the name BarChartQ
.
```
TRANSFORM Sum(Stock1.Val) AS SumOfVal
SELECT Stock1.Desc
FROM Stock1
GROUP BY Stock1.Desc
PIVOT Stock1.Date;
```
Open a new Report in the design view. Select Object. . ., from the Insert Menu (Chart Option on the View Menu uses only six Fields for a Chart), select Microsoft Graph Chart, and click OK. A Chart Object with default values is inserted into the Report.
Click outside the chart on the report to deselect the chart from Edit mode. Click again on the chart to select it, display the property sheet, and change the following values:
- Size Mode = Zoom.
- Row Source Type = Table/Query
- Row Source = BarChartQ
- Column Heads = Yes
- Left = 0.3"
- Top = 0.3"
- Width = 6.0"
- Height = 4.0"
Double-click on the Chart. The Chart Formatting Toolbar will appear at the top. Select Column Chart from the Chart Type Toolbar Options, or Right-Click on an empty area within the Chart and select Chart Type from the Shortcut Menu, and select Column Chart.

Click to Enlarge
In the design view, the Chart object displays a chart with sample data in an Excel-like data Sheet (if not visible, double-click on the chart). But, when the Report is print-previewed, the Chart will appear with the actual data from the Table/Query we have attached as the Source. To see the result of the Source Data in Design View, also copy and paste the Source Data into the Excel-like Cells.

Minimize the Report you're working on and switch to the Queries tab.
Double-click BarChartQ to open it.
In the Datasheet view, click the top-left corner to select all records, then click the Copy button (or choose Edit > Copy).
Restore the Report containing the Chart and double-click the Chart to open its Data Grid.
Again, click the top-left corner of the Grid to select all cells, and paste the copied data (use the Paste button or Edit > Paste).
To remove any extra rows, click the left border of the unwanted lines below the data and choose Edit > Cut.

The Chart in Design View will now reflect the actual data values.
Chart Formatting for a Visually Pleasing Look.
We will format the Chart to give a better appearance.
Removing the Plot Area Shading for a Cleaner Look.
If the Chart is still open after pasting the data into the Data Grid (or double-click the Chart to enter editing mode), follow these steps:
1. Right-click at the center of the chart, specifically in the shaded background away from the chart bars or lines.
2. When the Plot Area is highlighted, select Format Plot Area… from the shortcut menu.
3. In the Format Plot Area dialog:
  - Go to the Area Options section.
  - Select the None option to remove the background fill.
4. Click OK to apply the changes and close the dialog.
This will remove the shading behind your data series, resulting in a cleaner, more professional-looking chart.@@@
Right-click on one of the Bars and select Format Data Series, click on the Fill Effect Button, and select the Gradient Tab.
Click Vertical under Shading Styles and click on the Right Bottom Style, out of the four Styles displayed. If you want to change the Color of the Bars, you can select the color of your choice from the color palette. Accept the default One Color Option without change.
Select the Data Labels Tab. Put a check mark in the Value Option, and click OK to update the change on the Graph.
If the Data Label Font size is large, then right-click on one of the labels and select Font, Font Style, Size, etc., from the Menu, and change the values to your liking (say 10 points). Repeat the same change in X-Axis Labels, Y-Axis Labels, and Legend.
Right-click on the Chart outside the Plot Area and select Chart Options.
Type NYSE Index in the Chart Title Control.
Save the Report with the Chart and open it in Print Preview.

Click to Enlarge
Add two more records to the Chart's Table Stock1. Print Preview the Report with the Chart again and check whether the newly added records are appearing as new Bars on the Chart.

Creating a Line Chart from the Existing Bar Chart

You can easily create a Line Chart using the same data as your Bar Chart by duplicating and modifying it:

In Design View, click on the existing Bar Chart to select it.
Press Ctrl+C to copy, then Ctrl+V to paste a duplicate chart below the original.
Double-click on the new chart to open it for editing.
Right-click on an empty space inside the chart area (but outside the plot area) to bring up the shortcut menu.
Choose Chart Type from the menu.
In the list of available chart types, select Line Chart and click OK.

Now, your new chart will display the same data in a line chart format.

Introduction.

Data Source Queries.

The Three-Part Approach.

Part-A:

Part-B:

Part-C:

Introduction.

Tracking Down the Real Issue.

Modifying the Chart Data Source Query.

Introduction.

PIE Chart Options.

Sample run: an image of Option-1:

Sample run: an image of Option-2:

Sample run: an image of Option-3:

Run it from the Command Button Click.

Chart Formatting.

Download the Demo Database.

Introduction.

Sample Demo Run Code

The VBA Code

Preparing for Demo Run

Saving the Report with the Chart.

Saving PowerPoint Presentations.

Download the Demo Database.

Introduction.

Attaching System Reference Library Files

Formatting Chart Elements

Color Numbers

Preparing for Trial Run

2D Chart Area Zones

The VBA Code.

Running the Code

Download the Demo Database.

Continuation of Last Week's discussion

Creating and Formatting a 3-D Pie Chart.

A chart with more than one set of Bars.

Customizing Chart Y-axis Scale Values

Formatting Data Labels.

Secondary Y-axis Usage.

Adjusting Bar Width.

Access Reports and Graph Charts.

Preparing for a simple Chart.

Charts in Excel and MS-Access.

Line and Bar Charts.

Chart Formatting for a Visually Pleasing Look.

Removing the Plot Area Shading for a Cleaner Look.

Creating a Line Chart from the Existing Bar Chart

PRESENTATION: ACCESS USER GROUPS (EUROPE)

Translate

Post Feed

Get new posts by email:

Search

USEFUL LINKS

Popular Posts

About Me

Blog Archive

Labels

Statcounter