Trouble with making a chart from a crosstab query

[birdbraintv]

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.

[isladogs]

Welcome to the forum

Does that query SQL give the correct results?
If yes, the issue is applying the query to a column chart.
If no, then the query needs modifying
Can you clarify which is true

[birdbraintv]

Welcome to the forum

Does that query SQL give the correct results?
If yes, the issue is applying the query to a column chart.
If no, then the query needs modifying
Can you clarify which is true

The SQL gives the correct query, applying the query to a column chart is the issue. When it gives the options of the fields to place into the column chart, it gives the individual months as individual fields ("jan","feb","mar"), and it gives every single bird species as one single field titled "BirdSpecies". I would like the opposite. I want all of the months as one single field("months"), and the bird species as individual fields for every individual species ("birdsp1","birdsp2","birdsp3").
Thank you for replying.

[isladogs]

There are two types of chart if you are using Access supplied with Office 365.
If using the older type, try editing it manually by entering the crosstab query SQL as the chart row source. You'll then need to edit the fields used on each axis.
If using 'modern chart's you need a different approach but I don't have that feature on the tablet I'm using now so can't easily advise.

If you can't solve it, please upload the relevant parts of your database explaining what you want and which type of chart you're using.
Someone will I'm sure be able to fix this for you.