Require some assistance in creating a bar chart

[chrismid259]

Hi all,
The title might suggest that I am slightly dim in that I want to simply create a bar chart. In fact, I need to create a specific bar chart that incorporates quite a fair amount of data.

For the purpose of this thread, I've exported the data from an Access table into Excel. The data that I have to create the bar chart includes; a customer ID number, Q1rating (through to Q9rating) and Date.

The bar chart will need to be a clustered bar chart. Preferably, I'd like the X axis to have Year (ie 2010, 2011) and then above have Q1 with a bar that includes; Excellent, Very Good, Satisfactory, Poor and Very Poor. This should also display the count for each catagory and a total at the top of the bar - this would then go on for each question and year.

I've attached the data to this thread. I'd appreciate it if someone could tell me how this can be done as I have no idea on how to create a bar chart that is this complex.



Thank you in advance.

[June7]

First four 4 records lack data in the Q8, Q9, Date fields. Other records are missing Date. Should these be excluded from the chart?

Numbers 1 through 5 represent the categories with 1 being Excellent?

You want the ratings aggregated by date? For instance, May 2010, all the ratings are 1. That is a total of 9 Excellent and none for the other categories in that period. This probably won't be easy.

[chrismid259]

First four 4 records lack data in the Q8, Q9, Date fields. Other records are missing Date. Should these be excluded from the chart?

Yes, exclude these from the chart - for now.

Numbers 1 through 5 represent the categories with 1 being Excellent?

1 = Excellent, 2 = Good, 3 = Satisfactory, 4 = Poor, 5 = Very Poor[/quote]

You want the ratings aggregated by date? For instance, May 2010, all the ratings are 1. That is a total of 9 Excellent and none for the other categories in that period. This probably won't be easy.

Yes, but only by year, not by month. If that makes sense.

I know it's not going to be easy at all. I have absolutely no idea where to start with this.

[June7]

Here's what I did, actually not as hard as I expected, already being familiar with UNION query:

1. Built UNION query to normalize the data. There is no designer or wizard for UNION, must type in the SQL View editor.

InfoUNION
SELECT [Date] As RecDate, "Q1" As Cat, Q1Rating As Rating FROM qryCustomerInformation
UNION SELECT [Date], "Q2", Q2Rating FROM qryCustomerInformation
UNION SELECT [Date], "Q3", Q3Rating FROM qryCustomerInformation
UNION SELECT [Date], "Q4", Q4Rating FROM qryCustomerInformation
UNION SELECT [Date], "Q5", Q5Rating FROM qryCustomerInformation
UNION SELECT [Date], "Q6", Q6Rating FROM qryCustomerInformation
UNION SELECT [Date], "Q7", Q7Rating FROM qryCustomerInformation
UNION SELECT [Date], "Q8", Q8Rating FROM qryCustomerInformation
UNION SELECT [Date], "Q9", Q9Rating FROM qryCustomerInformation;

2. Built report and chart. I used the chart wizard to build simple bar chart. Don't worry about settings, just create the object.

3. Edited the chart's RowSource SQL to:
TRANSFORM Count(InfoUNION.Cat) AS CountOfCat
SELECT Year([RecDate]) AS RecYear
FROM InfoUNION
WHERE ((Not (Year([RecDate])) Is Null) AND (Not (InfoUNION.Rating) Is Null))
GROUP BY Year([RecDate])
PIVOT InfoUNION.Rating;

4. Edited the chart to select the stacked bar option. Format chart axis labels, titles, legend. This is not as easy as in Excel. Right click on chart > Chart Object > Edit (or Open), right click on the opened chart > Chart Options. Click Ok to close Options. Close the chart.

5. Open report to preview and save and close. Open in design view and should see your settings instead of the generic stuff.

The resulting chart has two stacks, one for each year, with 3 parts for ratings 1, 2, 3, none for 4 and 5.