Results 1 to 2 of 2
  1. #1
    teedee is offline Novice
    Windows XP Access 2007
    Join Date
    Jan 2011

    Crosstab Count query - show all rows+columns


    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,


  2. #2
    teedee is offline Novice
    Windows XP Access 2007
    Join Date
    Jan 2011


    Can anybody help me with this??

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 09-24-2010, 07:23 AM
  2. Show/Hide Columns in a Query
    By SCFM in forum Access
    Replies: 1
    Last Post: 02-23-2010, 08:04 AM
  3. Replies: 1
    Last Post: 02-05-2010, 08:33 AM
  4. Show/Unshow Columns in Query
    By simmurray in forum Queries
    Replies: 0
    Last Post: 03-28-2009, 10:03 AM
  5. Replies: 2
    Last Post: 11-09-2005, 02:51 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
Other Forums: Microsoft Office Forums - Senior Forums