• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2317
  • Last Modified:

Pivot Chart in Access

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
LizzJ
Asked:
LizzJ
  • 6
  • 3
  • 3
1 Solution
 
Kelvin SparksCommented:
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
 
LizzJAuthor Commented:
but, how to do as a standard chart?
0
 
LizzJAuthor Commented:
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
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
Kelvin SparksCommented:
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
 
LizzJAuthor Commented:
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
 
Kelvin SparksCommented:
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
 
Jeffrey CoachmanMIS LiasonCommented:
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
 
LizzJAuthor Commented:
Jeff,
How to create a standard chart in Access?
Sorry i'm new to this...
0
 
LizzJAuthor Commented:
i just found out i could insert a chart into a form... but no idea how exactly this could be done
0
 
Jeffrey CoachmanMIS LiasonCommented:
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
 
LizzJAuthor Commented:
Thanks a lot Jeff! I've done a chart according to your instructions.
0
 
Jeffrey CoachmanMIS LiasonCommented:
;-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

  • 6
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now