Solved

Pivot Chart in Access

Posted on 2009-05-13
12
2,283 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
 
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
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…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

747 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now