I can't my data to show up in a chart the way it needs to be!

[Guest_imported]

This may be a dumb question but I have been running around in circles with this and it's killin' me!

This looks like a long question but it's actually easy to understand so bear with me!!!

Sample of my data is like this...It is an Access table.

PROJECT CATEGORY 1998($) 1999($) 2000($) 2001 ($)
A R 1 2 2 0.5
B I 2 2 3 1
C S 3 1 2 1
D I 1 1 2 2
E I 2 2 1 1

I am trying to create a chart for "Cost by Category" to look kinda like this...


$ - with the sum for each category
for each year showing up here
in bars


1998 1999 2000 2001

The right data is there...i can see it when I do the query but the problem is because the Years are Field names instead of data, and the Category are data instead of Field names, the data comes out the OPPOSITE way on my chart as I need!!

I know there must be an answer because the data is there! maybe Access is just inflexible or maybe my table is set up wrong but can somebody help me!!! i would be so grateful!

 

[moonlight]

What you need is a Totals Query

In the TOTAL: Row...Use Group By (Default) for your Category field ... and Sum for all the totals 1998($)for example..

To display your table for the sum of 1998($) in the column for that field write... 1998:1998($) .. This will change the title of your colummn in the table view... Do the same for all the columns adjusting the value to represent each year

Hope this helps

 

[Guest_imported]

I seem to only be able to choose one field for the x-axis, e.g. one bar chart for 1998 that breaks down $ by Category...and because my dates span six fields, how do I get chart to display all of them?

My query works...but the chart comes out the opposite way that i need! It Groups By Category and then displays the different categories on the x-axis...and then breaks the bar chart for each category into years...but I need the other way around!

Do I have my table designed wrong by having the years in multiple fields? should there only be one field, "Year" instead?

Or is there a way to do this?

 

[Guest_imported]

Basically guys, i can get it to display when it's a Report but the Data Access Page is a different story!! WHY??!!!