Solved

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

Posted on 2011-02-16
17
675 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
ID: 34907717
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
ID: 34907807
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
ID: 34907950
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
ID: 34907974
" 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
ID: 34908261
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
ID: 34908666
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
ID: 34909442
"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
ID: 34909661
Please see attached document of the graphics needed.

thanks
ExcelGraphics.docx
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 34910442
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
ID: 34915248
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
ID: 34918243
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
ID: 34919005
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
ID: 34919555
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
ID: 34919718
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
ID: 34920360
<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
ID: 34921604
Thank you both!
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 34921830
;-)
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

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

919 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