From a query containing a Household ID and 5 quarterly values (Beginning of Year, End of 1st Quarter, End of 2nd Quarter, 3rd Quarter, End of Year), I would like to display these 5 values in a bar chart. In the Chart Wizard, I am limited to "You can select just 6 fields to use in your chart". So in placing the fields on the chart, I have the HHID in the Axis window, and the five value fields in the Data window, nothing in the Series window. I link the Report and Chart with the HHID field. I then name the chart, and choose no legend and click Finish.
Access displays 5 bars on the chart, but only 4 columns in the associated datasheet. When I select Chart Object Edit, it only shows a datasheet containing 4 values, and a chart with 4 bars to edit. In the print preview, there are actually 5 bars, but only 4 can be edited, and the 5th is left in its rough form. Is this an absolute limitation in Access 2007, or is there a way to extend its capability to 5 bars?
I suppose the 6 field limit includes one for the Legend, which I don't use. This leaves one for the Axis, and 4 Data Values.