Crosstab issue

[teedee]

Hi,

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 really need this to work, so any help would be greatly appreciated.

Thanks!

[NTC]

I think your issue is null vs 0.

you state: "there are no records for all the years selected"

what you probably need is a record with the value 0 appearing. Implement this at the source record set. Look into the NZ method using a calculated field.

hope this helps get you in the right direction.

[teedee]

The problem is, I cannot change the source records.

I am currently counting the records, and if for a specific bird a record does not exist that year, then the query should show a 0. This works when I select more than one bird.

Example:
This is what the query shows when I run it for "Bird A" and "Bird B" for the years "2008", "2009", and "2010":

BirdName 2008 2009 2010
Bird A_____0____4____3___
Bird B_____2____0____0___



However when I run it for only Bird A for those years I get:

BirdName 2009 2010
Bird A_____4____3__



And likewise when I only run it for Bird B:

BirdName 2008
Bird B_____2__



I hope you know what I'm getting at. I looked into using NZ with Count, but I could not find anything. Anymore help would be apreciated.

Thanks!

[NTC]

the data is of course correct.

BirdName 2008
Bird B_____2__

what you are trying to is to force display of no data

BirdName 2008 2009 2010
Bird B_____2____0____0___

how would the db know to stop at 2010? (rhetorical - but hopefully you see my point).

Crosstab, for all its complications is, in the end, just a display format. You have to have the data to start. Before you get to the crosstab stage - first you want a plain ole vanilla query of Bird B to display 0s in 09, 10. Solve this. One way, cheap & dirty, put in a 0 value for 09 and again in 10 so a record exists. Maybe not feasible if 1000s of birds though. Another way; make a 1 column table of years. Outer join this YearTable to your Bird table forcing a record per year....that will result in:

BirdName Year Count
B 2008 2
B 2009 (null)
B 2010 (null)

then apply your NZ to it, and use this query as your starting point to develop a cross tab layout.....

Hope this helps.

[vicky464]

Try this: In the crosstab properties under Column Headings, enter all the years (2008, 2009, 2010,etc). Then under the Value field of the crosstab (Bird count) set the properties to Format 0;0;0;0 where the last 0 is the format for Null values.

I just had a similar issue and this was a fairly easy fix.

Good luck.

[teedee]

NTC,

thanks for the help. I have another table witht the years and species in it that I want, but when I try and create and Outer Join I get a warning that says the following:

"The SQL statement could not be executed because it contains ambiguous outer joins. To force one of the outer joins to be preformed first, create a seperate query that performs the first join and then include that query inyour SQL statement"


I'm not exactly sure what I'm supposed to here. Any help would be apreciated. Thanks


Vicky,

Thanks for the reply, that does work, however this means that if I want to change the years to a different range, I have to go into the query design everytime and change those column headers. Does that make sense? I want those headers to be picked out of my "tempYear" table. I'm still trying to work it out. Thanks for your help though,

teedee