Solved

Attractive Charts in MS Access 2010?

Posted on 2011-09-17
19
4,005 Views
2 Endorsements
Last Modified: 2012-08-13
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)?

Thanks!
2
Comment
Question by:Orcadian
  • 7
  • 7
  • 2
  • +2
19 Comments
 
LVL 50

Expert Comment

by:teylyn
ID: 36553645
Hello,

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
0
 
LVL 2

Expert Comment

by:jan24
ID: 36553663
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.
0
 
LVL 50

Expert Comment

by:teylyn
ID: 36553722
@Jan24,

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
1
 
LVL 31

Assisted Solution

by:Helen_Feddema
Helen_Feddema earned 100 total points
ID: 36554456
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:
PivotChart.jpg
0
 

Author Comment

by:Orcadian
ID: 36554895
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
0
 
LVL 50

Assisted Solution

by:teylyn
teylyn earned 100 total points
ID: 36555157
@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 http://www.perceptualedge.com/, Jon Peltier http://www.peltiertech.com and Chandoo http://www.chandoo.org. The latter have lots of information specific to Excel, but most of that would apply to the Access charting tools, too.

cheers, teylyn
charts.xls
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 300 total points
ID: 36555429
Orcadian,

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?
;-)

JeffCoachman
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36555436
teylyn,

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

...it 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.

;-)

Jeff
0
 
LVL 50

Expert Comment

by:teylyn
ID: 36555441
Jeff,

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
0
What Security Threats Are You Missing?

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.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36555531
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.

;-)

Jeff




0
 
LVL 50

Expert Comment

by:teylyn
ID: 36555563
Possibly.

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

;-)
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36556707
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.

;-)

JeffCoachman


0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 36556904
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).
0
 
LVL 50

Expert Comment

by:teylyn
ID: 36557397
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.

42.

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
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36557834
teylyn,

In a nutshell, I was, ...in 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.
http://www.experts-exchange.com/M_4970136.html
http://www.amazon.com/Helen-Bell-Feddema/e/B001IU4WFA/ref=sr_ntt_srch_lnk_2?qid=1316390276&sr=8-2
...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?
;-)

...so 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.
Sales
Jan
Feb
mar
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.

;-)

Jeff
0
 
LVL 50

Expert Comment

by:teylyn
ID: 36557925
Jeff,

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
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36558086
t

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...
...you 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.


anyhoo,
Gotta sleep now, work in 8 hrs...

0
 

Author Closing Comment

by:Orcadian
ID: 36558280
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.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36560474
Great.

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.

;-)

Jeff
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

758 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

23 Experts available now in Live!

Get 1:1 Help Now