When no results from query then insert value 0.

[tomazm71]

Hi,



I'm trying to find a way to set the query which will show me the default 0 when there is no records from in one column. This column has a total Count included.

I know Nz function but it turns zero when there is NULL result and I don't have any records.

What are the other options? Is there a function that can show me the values 0?

Let's assume there is a column called Actors and there are years Years when they were nominated for Oscars

Smiths - 2006
Smiths - 2007
Stallonee - 2008
Seagalll - 2009

We have 2010 now and There are 5 nominees, we count how many times they were nominated

Smiths - 2
Stallonee - 1
Seagalll - 1

The question is how do I get Shfwarzeneeger to be shown if the count will not give me any results?

Thanks in advance.
Tom

[Minty]

You need a sub query or second query to get your actor and count, and left join that to your list of all actors.
Then you use the NZ(CountofNominations,0) to provide the missing zero's

[tomazm71]

Minty,

Thanks very much for your help. It has helped and actually even the Nz was not neccessary. The query below showed me 0 for Shfwarzeneeger.

SELECT Count(TblActors.YearNominated) AS CountOfYearNominated, TblNominee2010.Nominee2010
FROM TblNominee2010 LEFT JOIN TblActors ON TblNominee2010.[Nominee2010] = TblActors.[Actor]
GROUP BY TblNominee2010.Nominee2010;

Tom
*

[Minty]

You might want to group on TblActors.[Actor] rather than the nominees.
I suspect you'll miss some actors otherwise.