Crosstab Count query - show all rows+columns

[teedee]

Hi,



I am a failry new MS access-or, but I'm slowly getting the hang for this. I am having a problem though with one of my queries.

I have a query that counts records grouped by Bird Species names, and then crosstabs is by year. The Species names and years are selected out of a temporary table that is filled by the VBA code of a form. My problem is that if for a particular species there are no records for all the years selected, instead of returning it like this:


Name: 2008 2009 2010
BirdA 0 0 0

It just returns blank, with nothing in it at all.



Or instead of this:


Name: 2008 2009 2010
BirdB 1 0 3


It will only return:


Name: 2008 2010
BirdB 1 3




This is the SQL for the query:


TRANSFORM NZ(Count(tblBirdData.BirdRecordID),0) AS CountOfBirdRecordID
SELECT tblBirdSpeciesList.CommonName
FROM tblBirdSpeciesList INNER JOIN (tblBirdSamplingMetadata INNER JOIN tblBirdData ON tblBirdSamplingMetadata.BirdSampleID=tblBirdData.B irdSampleID) ON tblBirdSpeciesList.[AOU Code]=tblBirdData.BirdSpeciesCode
WHERE (((tblBirdSamplingMetadata.SampleYear) In (select SampleYear from tblTempYear)) AND ((tblBirdSpeciesList.CommonName) In (select CommonName from tblTempCommonName)) AND ((tblBirdSamplingMetadata.BirdSiteID)>0 And (tblBirdSamplingMetadata.BirdSiteID)<77))
GROUP BY tblBirdSpeciesList.CommonName
PIVOT tblBirdSamplingMetadata.SampleYear;



I hope this makes sense and I hope someone is able to help me, and tell me what I need to do to make this work. Thanks a lot,

teedee

[teedee]

Can anybody help me with this??