Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

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

Posted on 2011-02-16
17
692 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

790 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