Solved

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

Posted on 2011-02-16
17
664 Views
Last Modified: 2012-05-11
I need to create bar charts and pie charts within Access 2007, but I find these nothing other than frustrating. Is it easy to export a query (at the click of a command button) to Excel and have a bar chart/pie chart created? If so, please explain how this would be done.
0
Comment
Question by:newbie46
  • 9
  • 6
  • 2
17 Comments
 
LVL 4

Expert Comment

by:ChuckDeezel
Comment Utility
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.
0
 

Author Comment

by:newbie46
Comment Utility
Thank you, ChuckDeezel. Also, could the display of the Excel graphics be triggered by an Access command button?
0
 
LVL 4

Assisted Solution

by:ChuckDeezel
ChuckDeezel earned 200 total points
Comment Utility
Here is how to add the Access connection to an Excel sheet (from Microsoft Support Site):

Connect to Access data from Excel
To bring Access data that can be refreshed into Excel, you can create a connection, often stored in an Office Data Connection file (.odc), to the Access database and retrieve all of the data from a table or query. The main benefit of connecting to Access data instead of importing it is that you can periodically analyze this data in Excel without repeatedly copying or exporting the data from Access. After you connect to the data, you can also automatically refresh (or update) your Excel workbooks from the original Access database whenever the database is updated with new information. For example, you may want to update an Excel summary budget report that you distribute every month so that it contains the current month's data.

Click the cell where you want to put the data from the Access database.
On the Data tab, in the Get External Data group, click From Access.
 

In the Look in list, locate and double-click the Access database that you want to import.
In the Select Table dialog box, click the table or query that you want to import, and then click OK.

In the Import Data dialog box, do the following:
Under Select how you want to view this data, do one of the following:
To view the data as a table (table: A collection of data about a particular subject that is stored in records (rows) and fields (columns).), select Table.
To view the data as a PivotTable report (PivotTable report: An interactive, crosstabulated Excel report that summarizes and analyzes data, such as database records, from various sources, including ones that are external to Excel.), select PivotTable report.
To view the data as a PivotChart (PivotChart report: A chart that provides interactive analysis of data, like a PivotTable report. You can change views of data, see different levels of detail, or reorganize the chart layout by dragging fields and by showing or hiding items in fields.) and PivotTable report, select PivotChart and PivotTable report.
Optionally, click Properties to set refresh, formatting, and layout options for the imported data, and then click OK.
Under Where do you want to put the data? do one of the following:
To return the data to the location that you selected, click Existing worksheet.
To return the data to the upper-left corner of the new worksheet, click New worksheet.
Click OK.
Excel puts the external data range in the location that you specify.

-----------------------------------

Now create a chart as you normally would within Excel using the fields from the Access database as it relates to the cells in Excel.

Also, you could technically write a VBA macro that would create a button and that button could launch a specific Excel sheet (IE your chart).

For info on that see:
http://office.microsoft.com/en-us/access-help/introduction-to-macros-HA001214202.aspx

(Microsoft Article on Macro basics for Access).
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
" 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
0
 

Author Comment

by:newbie46
Comment Utility
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.
0
 

Author Comment

by:newbie46
Comment Utility
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.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
"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.
0
 

Author Comment

by:newbie46
Comment Utility
Please see attached document of the graphics needed.

thanks
ExcelGraphics.docx
0
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

 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
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
0
 

Author Comment

by:newbie46
Comment Utility
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.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
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
0
 

Author Comment

by:newbie46
Comment Utility
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?
0
 

Author Comment

by:newbie46
Comment Utility
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?
0
 

Author Comment

by:newbie46
Comment Utility
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.
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 300 total points
Comment Utility
<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?>
I don't know think there is a direct way to do this. The data to generate the chart is a Crosstab query.
So even if you change the Datatype in the table to "Currency" and format the field as currency or convert the field to currency, .... or do all of the above, it still seems to not want to be formatted.


<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?>
Probably for the same reasons I listed above...

<Also, how can I change the ordering of the Categories, using your database as an example?>
This is a function of the "Transform" that is being done behind the scenes to create the chart summary.
If you notice the Pie charts (that do not use the Transform), display Alloted first.
;-)
You can modify the rowsource of the Bar chart to reverse the order, and this should give you what you want.  Something like this roughly:
TRANSFORM Sum(YourTable.Amount) AS SumOfAmount SELECT YourTable.Department FROM YourTable GROUP BY YourTable.Department ORDER BY YourTable.Category DESC  PIVOT YourTable.Category;

JeffCoachman
0
 

Author Closing Comment

by:newbie46
Comment Utility
Thank you both!
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
;-)
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

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

763 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

8 Experts available now in Live!

Get 1:1 Help Now