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