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

    Crosstab Count query - show all rows+columns

    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

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

    Anyone??

    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