Need Chart in Access Report to Not Aggregate

[spacetanker]

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.

 

[boblarson]

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.

 

[emorris1000]

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?

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.

 

[spacetanker]

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

 

[emorris1000]

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.

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

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.

 

[spacetanker]

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

 

[emorris1000]

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?