Solved

Pivot Chart in Access

Posted on 2009-05-13
12
2,284 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

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…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

895 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