Display null returns

[Numpty]

I have created a crosstab query which displays the number "Delegates" within our database who fall into certain ethnic categories, also listed by sex.
The query I have created so far will display the count of delegates within a category as long as it has an entry. What I would like it to do is also display zero if we have no delegates in that category. That is where my problem lies, I can't get that part to work.

The expression I am using is:

(IIf(Count([DelegateID])>0,Count([DelegateID]),0))

The full SQL for the query is:

TRANSFORM (IIf(Count([DelegateID])>0,Count([DelegateID]),0)) AS [Count]
SELECT [EthnicOrigin].[EthnicGroupID], [EthnicOrigin].[EthnicGroup]
FROM Delegates INNER JOIN EthnicOrigin ON [Delegates].[EthnicGroupID]=[EthnicOrigin].[EthnicGroupID]
GROUP BY [EthnicOrigin].[EthnicGroupID], [EthnicOrigin].[EthnicGroup]
PIVOT [Delegates].[Sex];

Any help greatly appreciated and will keep my hair in its rightful place for one more day........

 

[dcx693]

I may be total off here, but perhaps you could try using the Column Headings option in the crosstab query to force it to display zeroes. Then again, you might be forced to type in all the other values you want displayed also. Or, use Excel to create a pivot table.

 

[Numpty]

Cheers Dcx,

I don't know what you mean about forcing the column headings to display the zeros though.

I've tried to change my expression to use the Nz function but it still won't display the null values.

Nz((Count([DelegateID])),0)

Any idea's?

 

[CJBIRKIN]

Hi

Try using the Nz function thusly, you never know it may work!!

Count(NZ([DelegateID]),0)


Chris

 

[Numpty]

Worth a try!

Unfortunatly produces the same result though.....

Any other ways to substitute a zero for a null????

 

[Rich]

You've too many brackets and don't need the Iif
Nz(Count([DelegateID]),0)

 

[Numpty]

That gave the same result as the others Rich.

But.... I have now managed to get it to display the Nulls by changing the Join properties in the query to the 3rd option and include ALL records from EthnicOrigin but it now also displays and extra Column <> which is populated entirely by zeros.

Fizzio I'm using Access2000 so I'll have a look at that example now.

Thanks for all your help everyone - I'll let you know how I get on.

 

[Numpty]

Where did that get to

 

[Fizzio]

I deleted it as Rich's solution worked
I'll post it again just for reference. (don't laugh though!)

 

[CJBIRKIN]

Hi

How does this sound,

You need to display all of the ethnic groups and show the number of delegates in each group also split by sex.
So row is sex
column is Ethnicity
value = count of delegates.

I just tried this and the only way to get all the ethnicities displayed is to use a right join i.e show all rows from the table with the ethnicity descriptions and all rows from the delegate tbl where the join is equal.

In this case we know we will always find a match in the delegate table so all the records will be returned. Great.

The problem is the sex (This isn't personal! ). If you return all the ethinic catergories and all the delegates you end up with three rows 1 for male 1 for female and 1 for null where there is no delegate for that ethnic category and therefore no sex.

Saying all of that try this DB


Chris

 

[Numpty]

Cheers Fizzio,

I see how that would work - although I'm doing about 12 similar queries so would have to repeat that 12 times!!

 

[Rich]

IIf(IsNumeric(Count([DelegateID])),Count([DelegateID]),0)

 

[Numpty]

Cheers for that one as well CJ.

I wouldn't know how to get the sex into it though

I've basically done what you said though and have included ALL from EthnicOrigin so it does now show three columns. I've hidden that column when the query runs so the result looks good now. Although I intend exporting that and several other queries to excel at a later date so don't know if it will export the <> column.

 

[Numpty]

That one works as well rich but will still display the extra column in the query result.

anyway I can hide that so I'm happy enough for now!

Once again...


Thanks