Solved

Pivot Chart in Access

Posted on 2009-05-13
12
2,286 Views
Last Modified: 2013-11-28
Hi Experts,

I'm struggling with pivot charts in Access. I've a simple bar chart built on a query. Is there a way to amend the legends, create a title and display the actual figures of each bar on the chart like what we can do with Excel charts?

Also, how to copy the charts into a powerpoint? I can only copy the figures but not the chart itself...

Please shed some light...
0
Comment
Question by:LizzJ
  • 6
  • 3
  • 3
12 Comments
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 24381902
I struggle to comprehend why users use Pivot Charts in Access unless you need the user to interact with them - whic is usually not the case. The standard chart is easier to setup, can be conrolled via VBA for titles, labels and turn on & off whatever features you want - although the learning curve there is not for the faint hearted.

Exporting a chart - you need to export as a jpeg, and then include the jpeg into powerpoint, word etc.....

The function inthe code below will manage that


Kelvin
Private Sub cmdPrint_Click()
On Error GoTo Err_cmdPrint_Click
 
    Dim stDocName As String
    Dim MyForm As Form
 
    stDocName = "frm_EPI_Weekly"
    Set MyForm = Screen.ActiveForm
    DoCmd.SelectObject acForm, stDocName, True
    DoCmd.PrintOut
    DoCmd.SelectObject acForm, MyForm.Name, False
 
Exit_cmdPrint_Click:
    Exit Sub
 
Err_cmdPrint_Click:
    MsgBox Err.Description
    Resume Exit_cmdPrint_Click
    
End Sub

Open in new window

0
 

Author Comment

by:LizzJ
ID: 24381920
but, how to do as a standard chart?
0
 

Author Comment

by:LizzJ
ID: 24381949
Okay, the question might be more appropriate if I ask, how can I export the query to Excel, and do up the chart in Excel using VBA in Access?
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 24381996
docmd.TransferSpreadsheet acExport,acSpreadsheetTypeExcel8,"queryName","Path  and fielname for export",True

Use Flse at the end if you do not want the fieldnames as column headings


Kelvin
0
 

Author Comment

by:LizzJ
ID: 24382012
Thanks Kelvin.

I'm also looking solutions on how to do up a chart in Excel via VBA. Maybe I should post another question on this, or can you provide a guide here as well?
0
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 24382049
I don't know Excel that well. If using VBA, download the graph VBA user guide off the net. You must set a reference in VBA to MS Graph before you can use it and most of the interactive predictive code doesn't work. What I generally do, is setup a to record a Macro, create what I want in the graph. Then view the VBA the macro created, and relate that back to my code. You'll really stuggle to find good online resources in thia area.

Help file is at

http://msdn.microsoft.com/en-us/library/aa171197(office.11).aspx


Kelvin
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24382109
LizzJ,

Everything you are requesting:
<amend the legends, create a title and display the actual figures of each bar on the chart >
...can be done in a "standard" Access chart.

If you can create a standard chart, I will be happy to show you how.

JeffCoachman
0
 

Author Comment

by:LizzJ
ID: 24382250
Jeff,
How to create a standard chart in Access?
Sorry i'm new to this...
0
 

Author Comment

by:LizzJ
ID: 24382263
i just found out i could insert a chart into a form... but no idea how exactly this could be done
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
ID: 24387024
There is Wizard to help you though the process.

But I thought you said you "had" the chart already?:
< I've a simple bar chart built on a query. >

In any event, here are the steps to do what you originally requested:
The steps below are the same for each operation:
a. Open the chart in design view
b. Right click the chart and select: Chart Object-->Edit

<amend the legends>
(Complete steps a and b)
Click the Ledgend, then right click the ledgend, and then select: Format Ledgend  (or "Clear" if you want to delete it)
Now, if you want to edit a ledgend "element", click the ledgend element "after" you have clicked the Ledgend.

<create a title>
(Complete steps a and b)
Right click the chart and select: Chart Options
Select the "Titles" tab

<display the actual figures of each bar>
(Complete steps a and b)
Right Click the bar and select: Format Data Series
Then Click the "Data Labels" tab.

It's a little tricky at first, but once you get the hang of it, it's pretty straigntforward.
(If you notice the dialog boxes are the same and they are in Excel)
The steps are the same if the Chart is in a Form or a Report

;-)

Jeff

0
 

Author Comment

by:LizzJ
ID: 24391840
Thanks a lot Jeff! I've done a chart according to your instructions.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24392143
;-)
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Access Update Query 1 20
Dlookup MSACCESS 5 25
Opening forms with an input box 5 22
Microsoft Access Delete all Records from table but Max 2 3
In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question