I'm having some confusion on how to make a chart from a crosstab query. Whenever I go to create the chart, the fields that I would like to include do not show up. My data includes percentages of bird species sightings by days per month. It looks somewhat like this:
BIRDSSEEN JAN FEB MAR
birdsp1 10% 20% 30%
birdsp2 5% 5% 10%
birdsp3 20% 20% 25%
I want to make a column chart for every bird species, which would include the months on the X axis and the percentage on the Y axis. When I go to select the fields, instead of pulling up the different birds species and Date, it pulls up every Month as a separate field and all the bird species in one field. If I were to chart this, I would only be able to add 4 months, the bird species field, and the percentage field, due to Access' limit to 6 fields per chart. I've tried switching the Date field and BIRDS SEEN between row heading and column heading, but it seems to do nothing but change the design. Here's the SQL:
TRANSFORM Sum(DailyObsBirds.BirdPresAbs)/623.2*100 AS SumOfBirdPresAbs
SELECT DailyObsBirds.[Birds Seen], Sum(DailyObsBirds.BirdPresAbs) AS SumOfBirdPresAbs1
FROM DailyObsBirds
GROUP BY DailyObsBirds.[Birds Seen]
PIVOT Format([Date],"mm");
Any help would be appreciated, thank you.