Why doesn't Query show zero for records that don't fit my criteria?

[rushtona]

I'm trying to build an Access Query that will give me the year and the total catch, even if there was no catch. The problem is if there was no catch for a certain year the query is not showing that year.

I've attached a picture of the query criteria. I've tried multiple things to get it to show all the years (should be back to 1952, but it's only showing back to the first in-river data in Area 29 which is my critieria I specified). Why won't it show a 0 for all the other years that don't fit my criteria?

I hope this isn't too confusing. Can someone help?????

[missinglinq]

The answer is very simple; if a record doesn't meet the criteria, it's not included in the query!

"I Want" says absolutely nothing about your problem here! I’ve re-titled your thread so that it actually reflects the question at hand. Having a title that does this clearly is important for two reasons.

First, it allows members, at a glance, to understand the nature of the question being asked. Thus, people who have never dealt with this or similar problems are saved the time and trouble of opening your thread. They would have nothing to contribute.

Secondly, and just as important, responsible people with questions first search for threads that address similar issues. Having a clear title facilitates these searches and saves everyone time and trouble.

Welcome to Bytes!

Linq ;0)>

[MMcCarthy]

you used the NZ() function but left out an important element. You have to tell the function what value you want returned if null. So try this ...

Sum(NZ([Catch],0))
 

[missinglinq]

@msquared
Actually, you really don't, if you're willing to settle for the Default. I didn't try this with an aggregate function, but assuming Field1 and Field2 are defined as Numbers, and

Field1 = 10

and

Field2 is Null

Nz(Field1) + Nz(Field2)

will yield 10, not a Null.

Access looks at the Datatype of the underlying field in question, and assigns an appropriate value if the argument is left blank.

If the Datatype is Number it assigns a zero.

If the Datatype is Text it assigns a zero length string.

Maybe I've misinterpreted the problem; the OP's question isn't terribly clear, but he does say

"Why won't it show a 0 for all the other years that don't fit my criteria?"

and if by "criteria" he means where [Marine/In-River] = "in-river" and [AreaID] = 29, then the query is not going to show records that don't meet this criteria.

Linq ;0)>

[MMcCarthy]

Hi Linq :)

My understanding is that OP is looking for records to be returned that satisfy all criteria but [Catch]=null for that year. To return those records then [Catch] has to resolve to 0. That's what I think is going on anyway.

Mary

[rushtona]

OK, I understand now that I can only get the data for the specified criteria. Sorry if I don't explain myself very well. I don't know all the terminology involved here.

The reason I was trying to get the query to show all the years, even if there was no catch, was so that I could link this table (tblCommercial) that contains catch for the years 1980-2008 with another that contained data for the years 1950-2008 (tblIFF). The tables were linked using the year field. I spent 3 days trying to get this to work before posting my question. I realize now I was focusing on the wrong problem. I should have been working on the joins between the tables in the new query. Once I made the join to show all the data from tblCommercial and only the ones from tblIff that were equal it all worked out.

Thanks to those that responded with helpful remarks. Appreciated. It always helps to discuss with other people. Cheers!