Add in a value for a code that has no record

[sillyr]

Hello - I am using Access 2007. I created a query in which information on species catch is queried from 3 tables that are joined by trip and haul. If a species was not reported for a unique trip haul then there is no species code or catch value. I was wondering if there is a way to add in the species_code with a 0 for species_kept_weight in the query.
For example:
for species_code 818
for trip 1 haul 1 there was a weight recorded, but for trip 1 haul 2 there was no weight recorded so I would like to add in species_code 818 and a 0 in the species_kept_weight
Right now when I run the query trip 1 haul 2 is not included because there is no record for species_code 818
trip haul species_code species_kept_weight
1 1 818 100
1 2 818 0
I apologize because the names of the data fields are not in a correct Access format, but I did not create the database.
Thank you for any help
Sally

[MikeTheBike]

Hi

I think, maybe, you need a LEFT JOIN (or RIGHT depending on table order in the query).

You may then want to include the 'Field' below in the query designer.

species_kept_weight: Nz(TableName.species_kept_weight,0)

The Nz() funcion replaces all Null field values with a zero (in this instance).
More info can be found in Help!

Cannot be more specific without seeing the actual query??

HTH

MTB

[NeoPa]

Mike's on the money there Sally.

Whenever a query includes OUTER JOINs, then there is the potential for records within the incoming data to be missing. I mean the entire record of one of the tables joined in such a way of course, rather than all the incoming data necessarily of course. All of the fields from this missing input record are represented as Nulls in the query. It's not the only way Nulls can appear in your data by any means, but it always indicates a missing value (Null is not a value as such - it is simply a representation of the absence of any value).

Mike's solution of using Nz() then, is a good one to handle such a situation.