?
Solved

Pivot Chart in Access

Posted on 2009-05-13
12
Medium Priority
?
2,312 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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 2000 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…
Suggested Courses

616 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