Automating the creation of bar/pie charts in Excel from Access 2007 query export

You can create an Excel sheet using the Access database as the datasource. Then when you create the chart in Excel, you can use the fields from the db to populate the chart. I will give you further details in just a bit.

Thank you, ChuckDeezel. Also, could the display of the Excel graphics be triggered by an Access command button?

" but I find these nothing other than frustrating."
So are you saying that if someone could explain it in a less frustrating way, you would not be interested?
(In Excel can you easily click a button/use a drop-downbox and modify the chart? Change the series, filter, ...etc)
...you can in Access...

Every chart I can do in Excel, I can do in Access.
Yes, ...the chart utility in Access can be .... "frustrating"
;-)
...but once you know the specifics, manipulating the chart is basically the same as in Excel.

Besides, jerking around with imports and exports of data/chart between Excel and Access might be more frustrating...
Not to mention the issues if either file is moved, renamed or otherwise modified as to break your "Link"...

In most cases, keeping your System confined to one application is the most straightforward approach.

up to you really...

JeffCoachman

boaq2000,
Yes, if someone could explain how it works, I would be interested. I opened a query in Datasheet view and selected Create Pivot chart. After I managed to get the fields dragged and dropped, I had an actual bar graph. Then, I was wanting the values to display under the bar graph, and could not find where that option was. Then, when attempting to save this newly created pivot table, it prompted me for a name. I gave it a unique name (different from the query in which its data was located), but after saving it, I could not find it. It did not appear within the listing of Queries. I'm not sure where it went. Just a few examples of the frustrations.

ChuckDeezel,
Thank you very much for the very detailed instructions. I am going though each step and will get back to you when I have completed them.

"Automating the creation of bar/pie charts in Excel from Access 2007 query export"
So, ...Do you need a Bar chart or a Pie Chart?

Do you need a standard chart or a Pivot Chart?

In other words, posts a sample of your raw data, then post a Graphic of the exact output (Chart/Graph) you are expecting based on the sample data.

Please see attached document of the graphics needed.

thanks
ExcelGraphics.docx

Same basic charts in Access.

Again, the data needs to be restructured/Normalized for true "database" use.
And, yes, it will take a few trials to learn charting in Access.
But once the chart is set up, there will probably not be not that much of a need to do anything to it but minor tweaks.
With this setup, the only thing you need do is open the report...

Examine the sample carefully, post back if you have any questions.

;-)

JeffCoachman
Access-EEQ26825636-ExcelChrtGrap.mdb
untitled.JPG

boaq2000,
The charts look great! Thanks.

I have a few questions:
1) How did you get the data to display under the bar graph? I looked around in Properties to see where there is a checkbox to display the data (but didn't find anything)
2) How do you change the coloring associated with Obligated, Expended and Allotted
3) I added the actual percentages to the pie chart. Clicking on a pie chart section, then clicking properties, it took a few tries to get the property sheet in which I could add the percentages to display. What is the secret to getting the property sheet to add the percentages to display the first time?

thanks.

The key here is getting into "Edit Mode" for the chart.

Open the chart in Design view and double-click it.
(Notice that a "Toolbar" is now visible.)

Now you can simply right-click any chart element and change it, just like you can in Excel.

;-)

Jeff

Ok, just one more question. I need to change the numbers which appear in the Data Table to be of Standard type, so I will see 4,000 as opposed to 4000. How can this be changed?

boaq2000,
I was able to highlight the data sheet table, right-click, and select number to change to Custom. Then, in the data sheet, the numbers appear with the new Custom format. Yet, when I return to Print Preview, the numbers are not displayed in the Custom format. Why is this the case?

boaq2000,
Also, how can I change the ordering of the Categories, using your database as an example?
Instead of seeing Obligated first, followed by Expended and Allotted. I want to see Alloted first, followed by Expended, then Obligated. Thus, switching the order in which they are displayed in the bar chart.

thanks.

Thank you both!

;-)