Attractive Charts in MS Access 2010?

I am designing a few reports in Access 2010.  The reports are for upper management, and I'm finding that the charts that Access 2010 creates are ugly and old-fashioned.  Excel 2010 has attractive charts that look very modern and presentable, but Access is just embarrassing.    Is there any way to make attractive charts in Access 2010 (without purchasing third-party controls)?

ChrisIT ManagerAsked:
Who is Participating?
Jeffrey CoachmanConnect With a Mentor MIS LiasonCommented:

In a nutshell you are correct...
For some reason MS did not upgrade the Chart graphics of Access to be comparable to what is now available in Excel.

As a side not, I first noticed this in Acc07, but I had held out hope that 2010 would resolve this...
Obviously not...

There is no way that I know of to significantly "Enhance" the look of the charts in Access.

What you may be forced to do is to create the chart in excel, from "Linked" Access data.
In Excel, do this:
Data-->Get External Data-->From Access
Select the Database, Select the Table/Query
Select: "Table" as the view.

Now with the data linked to Excel, you can create the chart in Excel.

Note: to refresh the Access data simply click the Refresh/Refresh All button in Excel (on the "Data" tab).

...or just do the best you can with the Access charts...
Remember that the Access chart displays the exact same data, just not as pretty.
Beauty is in the eye of the beholder
For example:
1. If sales are down 50%, ...nobody will care about the "Chisled" edges of the pie chart slices...
2. Would you refuse a Million dollar check if it was printed out from a dot matrix printer?

Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:

I've never created a chart with Access 2010, but plenty with Excel in many versions. Post a screenshot of a chart you're unhappy with and I'll be glad to give you a few pointers on how to improve it.

cheers, teylyn
In theory it should be possible to write a macro in Access which opens an instance of Excel, creates the chart you want, and then copies it as a picture to the clipboard and pastes it back into the Access report.  Having said that, I've not done much programming with Access before so there may be pitfalls that I'm not aware of.
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:

creating meaningful and attractive charts is not something that can only be achieved with Excel. It's about knowing what works with data visualisation. Even the ugliest Access default can be improved considerably by applying a few charting best practices.

A macro to create a chart in Excel would be overkill, if the real issue is just the application of things that work.

cheers, teylyn
Helen FeddemaConnect With a Mentor Commented:
What kind of charts are you creating?  I have found that PivotCharts are quite attractive, and offer many choices.  Here is one I made in Access 2007:
ChrisIT ManagerAuthor Commented:
Hi All,

Thank you for your comments.  I will consider pivotcharts, though they still seem to have a "1998" sort of feel.  See the attached image comparing Excel 2010 charts with Access 2010 charts.  Excel charts have significantly more flexibility and options for styling the charts. Excel 2010 vs Access 2010 Charts
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Connect With a Mentor Microsoft MVP ExcelCommented:
@Helen, I hope you're joking. If not, please tell my how to figure out whether Michael or Laura has the higher figure in June. What is Janet's number for November? What number is bigger: Andrew in May or Nancy in April? Is Janet's number for November below or above 10,000?

These 3D column charts are useless. The foreground columns obscure background columns, the 3D has a disconnect from the gridlines.

@Orcadian, you can probably see that the 3D chart does not work, and it's not a matter of coloring or "attractive" (which is a very subjective measure, anyway).

A few notes re the pie charts you are showing:
- Use pie charts very, very sparingly. If you do, don't show more than 3 data points in a pie chart
- avoid 3D effects, since they only distort the chart elements (see the pivot chart above as a brilliant example)
- the pie chart on the right:
  -- Which channel has the biggest market share?
  -- Which value is larger: Billboard or Radio?
  -- How high is the market share of Internet?

It looks as if Access still uses the same charting engine as Excel 2003, so I've re-created the pie chart in Excel 2003. Then I created another chart off the same data. Here they are:

 pie chart
 bar chart
Look at the bar chart and answer the questions from above.
  -- Which channel has the biggest market share?
  -- Which value is larger: Billboard or Radio?
  -- How high is the market share of Internet?

The eyes don't have to flick back and forth between the data series and the legend to figure out which element represents which marketing channel. Sorting the data by value makes comparison of individual data points easier (but the chart still works with unsorted data)

This has nothing to do with effects or "attractive". The bar chart just follows a few simple best practices of charting, and they can be applied to charts created in Access, too!

If you are interested in learning more about these charting principles, check out the work of Stephen Few, Jon Peltier and Chandoo The latter have lots of information specific to Excel, but most of that would apply to the Access charting tools, too.

cheers, teylyn
Jeffrey CoachmanMIS LiasonCommented:

I think Helen's chart was just an example.

Obviously if you:
-had fewer Series/Datapoints,
-increased the size of the font
-used a different chart
-Zoomed In
...etc would be more readable.

Also remember that what Helen posted is a screenshot.
What may matter most is what it actually looks like printed out.
If that same chart was set up to be printed out on Ledger sized paper, my guess is that it would be more readable.


Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:

I clicked through to the full size screenshot, so I could read the labels. That size would just about fit on a letter sheet. But even if printed out on a billboard sized sheet, the 3D chart still suffers from the same problems:

- Data points overlay and obscure other data points,
- the distortion in the 3D makes comparison of near equal numbers impossible
- the 3D removes the connection to the grid and prevents even the eye-balling of a data point size on the grid.

Zooming and rotating will not be possible on paper, either, so I stick to my position :-))

cheers, teylyn
Jeffrey CoachmanMIS LiasonCommented:
Points all well taken, but again, I just think that Helen posted that as an example, ...just to show what you could do with a Pivot Chart in Access.

Had she picked another chart type it would have probably been clearer.



Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:

It also shows that "quite attractive" and "readable, informative, effective" are not necessarily the same thing.

Jeffrey CoachmanMIS LiasonCommented:
Yeah, that is the key.

I'm sure we have all had situations where "The boss" wants everything crammed on to one sheet.
They want something that "looks good", but displays tons of detail, ...without clearly defining what "Looks Good" means...

That is why I suggested linking the Access data into Excel.
Based on the graph the OP posted, the chart needn't be all that complex, only that it have access to the new Excel chart formatting options.



Helen FeddemaCommented:
Some people (clients) just want the data (as you say -- readable, informative, effective) -- while others are very concerned about appearance, almost to the point of neglecting functionality.  I did a large, complex database for one of my clients that did all they wanted (and a few more things I thought of).  I got paid, but not much in the way of praIse.  Then I added a little graphic of flames to a subform (it was called the "Hot Box"), and they were ecstatic!  I got more positive feedback about that flame graphic than on everything else put together in the entire database!

And yes, that screen shot was just an example of a PivotChart (from one of my books).
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Jeff, I've not worked with Access much.

Pulling the data into Excel to use the better charting engine makes a lot of sense. But the bar chart I posted as an alternative to the pie chart certainly can be created in Access, too.

Again, this is not about pretty colors or bevels. It is about the basics of data visualisation.

Unfortunately, due to the default settings in Excel 2003 (and earlier) and Acces charts, AND the fact that almost every chart in a Microsoft publication is a 3D chart, people are led to believe that these are the best charts to use. But they are not.

Sorry, Helen, but that goes for the pivot chart above, too. If that one is even published in a book, it encourages people to create inefficient charts like that.

Yes, management wants things in a certain way. But they do because they are not (yet) used to better charts, charts that inform instead of "look pretty".

With the tools we are given, it is so very easy to push a few buttons and create a chart. But before we even do that, we should ask a few questions. What do I want to show in the chart? There's some planning involved in that step, which often is neglected. The common approach is: I have all these numbers, let's do a chart. The Pivot chart above is an example. What does it show? 12 x 5 columns of varying height. It's not even possible to see which is the highest or the lowest column. What does management get out of that?

It would be much more useful to ask them a few questions first, figuring out what they want to learn from the charts:
- who is the best/least performing sales person? Over which time frame?
- what is the ranking of the sales people, according to their sales figures?
- what month had the highest sales overall?
- what are the changes in sales month by month? Are there any trends?
- what sales person showed the most/least growth over time?

All these are questions that can be answered with a glance at a few separate, well defined charts, that can be presented as a dashboard on the same page size as the 3D pivot chart.

Similarly the pie chart Orcadian posted. What does management want to learn from the chart?
- What is the most successful marketing channel? The top 2?
- How do the channels rank?

If these are the questions, then a pie chart cannot provide the answer.

My point is: A chart should be able to answer questions put to the data. If we don't ask the questions first, we can't expect the chart to provide the answers.


Getting off my soap box now.

But before I leave, here's a quote from Stephen Few, with regards to eye-candy in charts:

"Dressing things up is appropriate for advertising, because the illusion pleases and sells. When you’re responsible
for discovering the truth and understanding it, makeup only gets in the way."

And finally, another "quite attractive" chart that has been printed in a book, so it must be good.

 Business Objects User Documentation
Time trends with the months sorted in alphabetical rather than chronological order are so much more creative.

cheers, teylyn
Jeffrey CoachmanMIS LiasonCommented:

In a nutshell, I was, a roundabout sort of way, ...trying to hint to you that Helen is not just a run-of-the-Mill, ...Johnny-Come-Lately, Access Expert.
...Whether or not you knew this, (or cared), ...I did not know...
(In the same way that I researched your meteoric ascent up the ranks here before I made my post...)

It just seemed like some of your comments were a bit "abrasive" (for lack of a better word):

"I hope you're joking. If not, please tell my how to figure out whether..."
"Sorry, Helen, but that goes for the pivot chart above, too. If that one is even published in a book, it encourages people to create inefficient charts like that. "
"another "quite attractive" chart that has been printed in a book, so it must be good."

Now, ...I am in now way representing myself as Helen's defender, I just thought that as "experts" we could cut each other a little slack.

Again, as I stated and Helen reiterated, that that chart was just an example of what could be done in Access.
At that point in the thread, all we knew was that the OP was not satisfied with the "Look" of the charts.
Readability, clarity, complexity, ...ect were never mentioned by the OP, just the aesthetics.

This all being said, ...I agree with everything you posted as far as charting is concerned.
No argument there...

Being an Access Expert you might expect me to favor Access, but as you can see, that was not the case.
The issue with the Access charting engine is that it "assumes" that you will always want to "summarize" the data.
This is were most people get confused and frustrated with Access charting.
Then throw in a few unclear options about Linking fields to "change" the chart for each Record/Group
...and finally add in a convoluted procedure to even edit the chart...
And I understand fully why some Access folks run screaming away from charts in Access.
(If I had a Nickel for every post that states "Why is creating chats in Access so difficult?, why can't it be easy, like in Excel?", I would be a rich man)

In Excel, you get a chart of exactly what you selected.
And now with the enhanced graphics in Excel, ...Access charts do look kinda "sad"

But note my post:
1. If sales are down 50%, ...nobody will care about the "Chiseled" edges of the pie chart slices...
2. Would you refuse a Million dollar check if it was printed out from a dot matrix printer?
;-) AFAIC, as long as I can easily digest the info in the chart, I (personally) really don't care how "nice" it looks (3d, shading, lens flares, beveled edges, millions of colors, rotation, ...etc)
If sales are up 90% and I'm getting a $10k Bonus, you could scribble the chart on a cocktail napkin...

So I am with you in regard to putting a bit of effort into deciding on what chart will present the data in the most "informative" way.
So I always strive to first create the simplest, straightforward chart that displays the basic information.
Then leave it up to Management to decide on the cosmetics.

The other issue is that not all people will be able to absorb the info when presented in a certain way.
(I, (personally) have always had trouble understanding "stacked" bar charts...)
Some people find Line charts easier to understand, others prefer Bar Charts, Pie charts, XYScatter, ...etc
Some like Pivots Charts, other people can't stand them.
Some people like the data Horizontal, Sales, Jan, Feb Mar
...some like it vertical.
A lady where I work will drop 5 fields into a Pivot table and think its perfectly understandable, where I am totally lost.
...So I try to take things like this into consideration.


Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:

I am aware of Helen's track record and I stand in awe at these accomplishments and have the greatest respect for her work, although I've never really worked much with Access.

In the last few years, people like Stephen Few and Jon Peltier have started to challenge the system defaults and proved over and over again that effective charts have nothing to do with personal favourites. Even if I like a pie chart with 12 slices, that still does not mean that the message comes across. You make exactly that point with

>>I, (personally) have always had trouble understanding "stacked" bar charts...

Maybe the data being presented is not suitable for a stacked bar chart. Most stacked charts are hard to interpret. The fault most likely does not lie with the reader of the chart, but the wrong chart type being chosen for the data.

>> Some people find Line charts easier to understand, others prefer Bar Charts, Pie charts, XYScatter, ...etc

A chart that is chosen carefully to present the data and convey the intended message, will be easy to understand by any viewer.

My comments above may sound a bit harsh. But when I first saw Helen's post with the pivot chart, I really looked very long and very hard for an indicator that this was humor, a smilie, or a hidden wink. I spend a lot of time teaching people about effective data visualisation and could not believe that someone with Helen's experience would suggest such a chart with a straight face.

I apologise if I've stepped on toes.

cheers, teylyn
Jeffrey CoachmanMIS LiasonCommented:

Again, no sweat here...

But again, I think she just posted that as an example of you could do with an Access chart.
I don't think she meant it as an example of the "Best" way to chart... know, just something quick she had available in her "samples" folder.
(Not knowing that a mere "sample" would set off a "firestorm" response...)

In the same way that I might post a "Mega" report in Access with summaries, Groups, subreports, charts, page breaks, Conditional sums, Conditional Page breaks, running sums, Functions, Conditional formats, ...etc
...just as an "example" of what Access reports can do.

Gotta sleep now, work in 8 hrs...

ChrisIT ManagerAuthor Commented:
Thank you all for your comments and lively discussion.  I appreciate Jeff for answering my original question most directly.  Thanks to Teylyn for your insightful comments on selecting the best chart for the information you wish to convey.  And thank you Helen for informing me of Pivotcharts, as I was not aware the feature existed.
Jeffrey CoachmanMIS LiasonCommented:

But if all three of our posts helped, then you could have split the points.

If you want to change this, feel free to click the "Request Attention" link and ask that the points be split evenly between each of the three posts that helped most.


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.

All Courses

From novice to tech pro — start learning today.