Problem with update query

[D Newsham]

I am trying to run an update query and am having problems with null
values.

Starting from the beginning - I created a new table from my original
table, selecting distinct combinations of these rows:

Family, Genus, Species, Variety, Author

I then added an ID field to uniquely identify each of these rows.
Sample of data that may be in these rows:

taxa_id: 1
Family: Aceraceae
Genus: Acer
Species: circinatum
Variety: (is null)
Author: (is null)

The records vary as to how much information is in them. They may have
all of these fields, or only Family and Genus.

Now I would like to go back to my original table and assign this new
ID number to the corresponding record. I created a new field in the
original table, called taxa_id and am trying to run an update query to
fill in the new ID number:

"UPDATE table1, taxa SET table1.taxa_id = taxa.taxa_id
WHERE (((table1.Family)=[taxa].[family])
AND ((table1.Genus)=[taxa].[genus])
AND ((table1.Species)=[taxa].[species])
AND ((table1.Var)=[taxa].[var])
AND ((table1.Author)=[taxa].[author]));"

The problem is that it is only picking up 24 out of 2626 rows, because
it is not picking up any rows that have a null value in any of the
fields. Why would this be? I should be able to compare a Null to a
Null, right?

Thanks,
D.

[Tom van Stiphout]

On 30 Nov 2003 17:34:21 -0800, d_*******@hotmail.com (D Newsham)
wrote:

No, you shouldn't. NULL <> NULL. All the books say so.

I would go back to the original table and run that Distinct query
again. If that's not an option, perhaps the Nz function may help:
WHERE (((Nz(table1.Family))=Nz([taxa].[family]) ) 'etc.

-Tom.

I am trying to run an update query and am having problems with null
values.

Starting from the beginning - I created a new table from my original
table, selecting distinct combinations of these rows:

Family, Genus, Species, Variety, Author

I then added an ID field to uniquely identify each of these rows.
Sample of data that may be in these rows:

taxa_id: 1
Family: Aceraceae
Genus: Acer
Species: circinatum
Variety: (is null)
Author: (is null)

The records vary as to how much information is in them. They may have
all of these fields, or only Family and Genus.

Now I would like to go back to my original table and assign this new
ID number to the corresponding record. I created a new field in the
original table, called taxa_id and am trying to run an update query to
fill in the new ID number:

"UPDATE table1, taxa SET table1.taxa_id = taxa.taxa_id
WHERE (((table1.Family)=[taxa].[family])
AND ((table1.Genus)=[taxa].[genus])
AND ((table1.Species)=[taxa].[species])
AND ((table1.Var)=[taxa].[var])
AND ((table1.Author)=[taxa].[author]));"

The problem is that it is only picking up 24 out of 2626 rows, because
it is not picking up any rows that have a null value in any of the
fields. Why would this be? I should be able to compare a Null to a
Null, right?

Thanks,
D.

[TC]

If you want to compare two fields A and B, where either or both might be
null, but you want them to match if >both< are null, use this:

((A IS NULL AND B IS NULL) OR (A=B))
HTH,
TC
"D Newsham" <d_*******@hotmail.com> wrote in message
news:c8**************************@posting.google.c om...

I am trying to run an update query and am having problems with null
values.

Starting from the beginning - I created a new table from my original
table, selecting distinct combinations of these rows:

Family, Genus, Species, Variety, Author

I then added an ID field to uniquely identify each of these rows.
Sample of data that may be in these rows:

taxa_id: 1
Family: Aceraceae
Genus: Acer
Species: circinatum
Variety: (is null)
Author: (is null)

The records vary as to how much information is in them. They may have
all of these fields, or only Family and Genus.

Now I would like to go back to my original table and assign this new
ID number to the corresponding record. I created a new field in the
original table, called taxa_id and am trying to run an update query to
fill in the new ID number:

"UPDATE table1, taxa SET table1.taxa_id = taxa.taxa_id
WHERE (((table1.Family)=[taxa].[family])
AND ((table1.Genus)=[taxa].[genus])
AND ((table1.Species)=[taxa].[species])
AND ((table1.Var)=[taxa].[var])
AND ((table1.Author)=[taxa].[author]));"

The problem is that it is only picking up 24 out of 2626 rows, because
it is not picking up any rows that have a null value in any of the
fields. Why would this be? I should be able to compare a Null to a
Null, right?

Thanks,
D.