Go Back   Access World Forums > Microsoft Access Discussion > Reports

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 04-21-2011, 01:30 PM   #1
spacetanker
Newly Registered User
 
Join Date: Apr 2011
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
spacetanker is on a distinguished road
Need Chart in Access Report to Not Aggregate

Hello:

I have an Access database that contains readiness metrics for my Army Reserve battalion. I have created a number of reports, but am currently stymied in the creation of a chart on a report that displays historical information.

I have attached the table containing the source data. New records are appended to this table each week through a query that does all the aggregate functions to build the data for each week (I have simply copied the same data to cover three weeks for testing the chart). I am trying to develop a report that displays a chart (or two) for each UIC (Unit Identification Code).

Each chart will have the date along the X axis, and % along the Y axis, with series data from the seven "percent" fields: fldSTR, fldNMOSQPCT, fldEVALPCT, fldFCPPCT, fldAPFTPCT, fldWQPCT, and fldNPPCT. As previously alluded to, the series may be split into two separate charts if needed.

I have designed many reports in my day, but this is the first one that has required a chart and I am having much difficulty with what seems a pretty straight-forward problem. Since this is intended for historical data display, running the chart from the non-aggregated data will exclude the previous historical data, or at the least create an overly complex query!

Any assistance in making this behave will be appreciated.

Thanks all.
Attached Files
File Type: accdb BITRS - Copy.accdb (388.0 KB, 123 views)

spacetanker is offline   Reply With Quote
Old 04-21-2011, 01:38 PM   #2
boblarson
Smeghead
 
boblarson's Avatar
 
Join Date: Jan 2001
Location: Oregon, USA
Posts: 32,068
Thanks: 97
Thanked 1,825 Times in 1,578 Posts
boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold
Re: Need Chart in Access Report to Not Aggregate

Personally, I would create an Excel Chart and link to Access (a query) for the data. Excel charting is much more robust and not as limited as Access.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
boblarson is offline   Reply With Quote
Old 04-21-2011, 02:38 PM   #3
emorris1000
Newly Registered User
 
Join Date: Feb 2011
Posts: 125
Thanks: 27
Thanked 4 Times in 4 Posts
emorris1000 is on a distinguished road
Re: Need Chart in Access Report to Not Aggregate

I'm confused as to what the data is showing.

This table is the aggregate data of each field for each week? Is it the average? Do you have the raw data?

Why use a query to build this table? Why not just use the raw data and aggregate it as you see fit when you want to chart?

Quote:
Since this is intended for historical data display, running the chart from the non-aggregated data will exclude the previous historical data
I'm totally confused as to what you mean here.

----------

Anyways, all questions aside, after the horrible access graph wizard screws up and gives you the wrong thing (it always does) you can manually build the query that makes the graph, without aggregate functions. In design mode right click the graph, select properties, go to the Row Source item and enter the query you want to build the graph.

The first column of that query will be assigned as the x-axis and the rest will be assigned as y-axes.

As for having different unit IDs for each of these properties I would suggest breaking this up into multiple graphs. Otherwise you are going to have like 42 series (6 Units * 7 Metrics) which will look absolutely terrible. I would suggest giving people 2 options. 1 where you overlay every unit for 1 metric per graph, the other where you overlay every metric for 1 unit per graph.

emorris1000 is offline   Reply With Quote
Old 04-21-2011, 03:22 PM   #4
spacetanker
Newly Registered User
 
Join Date: Apr 2011
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
spacetanker is on a distinguished road
Re: Need Chart in Access Report to Not Aggregate

Bob:

Thank you for the suggestion, and I did consider that option for the reasons you specified.

Ultimately I would like to keep this project in Access because of how easily I can share the underlying tables, queries, and reports with others who need similar data for their units. Some may have more subordinate units reporting than I do, and thus a canned spreadsheet solution wouldn't work.

emorris:

What the table contains is the aggregate for each metric for all Soldiers within each unit. The non-percent fields contain the total number of Soldiers who are non-compliant in a given area. For example, fldEVALPCT shows the percentage of Soldiers with an overdue evaluation during the given week. Adjacent to the percent is another "eval" field with the number of Soldiers with an overdue evaluation during the given week. This same pattern is present for ach of the seven metrics.

I do have the raw data. These tables only contain information for the current week however, which is one reason I am not using the raw data to build the chart. The raw data is in the form of Excel spreadsheets once they have been exported from their official sources. I have linked these tables to the query that appends new records to the table I have provided. Thus, over time, I will have a single table that contains a history of all of the metrics I am tracking, by unit and date.

The chart needs to show each UIC's metrics over time, so having only the values for the current week won't be of much use. So, to elaborate on the quote you selected, since the raw data is for only one week, the resulting charts would not show the trend in the metrics over time.

Your final paragraphs may prove helpful, and I will experiment with them tomorrow. You suggest that I can build the query outside the Chart Wizard, then substitude the SQL into the chart Row Source through Properties? That said, if the first column is the date and the remaining columns the percentages, then how does the chart know to paint a new chart when the UIC changes?

The final report would have a chart (or charts, since the limit seems to be six fields) for each UIC. I agree, 42 series would make for a truly amazing "eye chart."

Thank you both for your suggestions thus far, and I hope this clears up any confusion.

Matt
spacetanker is offline   Reply With Quote
Old 04-22-2011, 09:18 AM   #5
emorris1000
Newly Registered User
 
Join Date: Feb 2011
Posts: 125
Thanks: 27
Thanked 4 Times in 4 Posts
emorris1000 is on a distinguished road
Re: Need Chart in Access Report to Not Aggregate

I think I have a better understanding now. While it's probably a bit late in the game, and this may be simply a matter of preference I would have that append query set up to append all of the raw data into the table instead of just the aggregates. But I am a bit of a data junkie and I always like to have the rawest data available, even if I plan on using aggregates.

Anyways, that aside. Sounds like you are going to use 1 of 2 methods to chart your info.

Quote:
That said, if the first column is the date and the remaining columns the percentages, then how does the chart know to paint a new chart when the UIC changes?
Sounds here like you are talking about having a single chart with like a combo box/list box that will let you select the UIC. For this you will need to do a couple of things. First the query you build for the chart will need to reference the combo box value as a conditional for the UIC (which, in the chart query will have the "show" box unchecked because you are only using the UIC to filter, you aren't plotting it.) Then you will want to put in an "after update" event into the combo box that requeries the chart. That should give you what you need.

The other option for that style is to have the chart linked to the combo box through master/child fields, but this always gives me a headache. You may have better luck though.

k, so that's the first way. Second way

Quote:
The final report would have a chart (or charts, since the limit seems to be six fields) for each UIC. I agree, 42 series would make for a truly amazing "eye chart."
Sounds like your report will want all of the carts one after the other on a single page. IFF the UIC values you have now are not really ever expected to change then you can hard code queries for each chart, manually putting the specific UIC values as conditionals in each one.

This is a bit brute force and not particularly elegant though.

I believe there is a better way to do it with a continuous form, but I don't have a lot of experience with those.
emorris1000 is offline   Reply With Quote
Old 04-26-2011, 07:56 AM   #6
spacetanker
Newly Registered User
 
Join Date: Apr 2011
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
spacetanker is on a distinguished road
Re: Need Chart in Access Report to Not Aggregate

After spending the better part of a day sifting through various forums, I found the incubus to a solution. The following is not a complete solution, but it is close!

There seems to be solid concurrence that the chart wizard in Access Reports is broken. I was never able to make it give me anything besides the dummy data, even with a very simple data set. So, instead of using the Chart Wizard for a report, I used the Chart Wizard for a form instead. It does not have the limitation on the number of series inherent in the Reports' chart wizard. I was able to build a chart with meaningful, albeit still with some problems, results.

I did all of the data manipulation in the Forms Chart Wizard. I still am having trouble making sense of the data, since it isn't displaying what I need to see, but at least it is there and can be edited. Once the form is set, I open the report in design view and just drag the closed form from the Access Objects pane into the form and resize. I used the report properties to link the tables, so now I have a single chart for each of my UICs!

My current dilemma is that I need to edit the legend label names so they make sense, and I am still struggling to get non-aggregated data to show in the chart, but at least the chart is intuitively satisfying. I'll post again once I figure out the rest of the problems!

Matt
spacetanker is offline   Reply With Quote
Old 05-02-2011, 08:24 AM   #7
emorris1000
Newly Registered User
 
Join Date: Feb 2011
Posts: 125
Thanks: 27
Thanked 4 Times in 4 Posts
emorris1000 is on a distinguished road
Re: Need Chart in Access Report to Not Aggregate

I do a lot of charting in access and I would say the chart wizard is broken pretty much everywhere. This may be mainly because I do a lot of scatterplotting of non-aggregated data, which may be the thing that it is worst at.

The legend names are defined by the field names in the chart query. You can change them by changing the resulting field names of the query for the chart (by doing the "UIC: UICID" thing.

What exactly is the non-aggregate data? In the table you linked all of the data is aggregated. Or do you mean you want the access chart wizard to stop aggregating your already aggregated data?


emorris1000 is offline   Reply With Quote
Reply

Tags
access 2007 , charts , reports

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Embedding Excel chart in Access report HartJF Modules & VBA 2 08-06-2010 06:10 AM
access 2007 crash report with Chart JohnAsaPrice Reports 0 04-13-2009 04:53 PM
Export Excel Chart to Access Report Automatically ray147 Modules & VBA 2 01-26-2006 08:57 AM
Simulating aggregate functions in an Access report andras Reports 0 12-30-2004 03:24 PM
[SOLVED] Paste link Access report (chart) into Word Ringo General 0 09-29-2004 06:32 PM




All times are GMT -8. The time now is 04:26 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World