Are joins possible if fields are null? 1

[smurfer]

I have an update query that checks to see if a certain number of semi-primary keys are equal and then either appends or updates the records. They are not true pk's but sort of act as them. However some of these pk's can be null, and my right join does not work on records containing nulls in any of the field, even when they are none in both tables being joined. Is it possible to use a right join with nulls somehow, if so please help! here is my code:
UPDATE rates RIGHT JOIN [Linked Rates] ON
([rates].[Lane to Point]=[Linked Rates].[Lane to Point])
AND ([rates].[Lane from Point]=[Linked Rates].[Lane from Point])
AND ([rates].[Expiration Date]=[Linked Rates].[Expiration Date])
AND ([rates].[Effective Date]=[Linked Rates].[Effective Date])
AND ([rates].[SCAC]=[Linked Rates].[SCAC])
AND ([rates].[Commodity]=[Linked Rates].[Commodity])
AND ([rates].[Equipment Type]=[Linked Rates].[Equipment Type])
SET rates.scac = [Linked Rates].[scac],
rates.Mode = [Linked Rates].[Mode],
rates.[Load Class] = [Linked Rates].[Load Class]

***there are more fields here but not needed for the explanation...

Thanks,
SM

 

[BNPMike]

smurfer

I think you must be angling for the Turing Prize here. I believe, although I may be wrong, you are the inventor of the semi-primary key.

The idea of a key (and whether it is primary or not is not important) is it can select a record. Forget the rule that nulls mean 'I can't participate', you couldn't in any event have a key with a null because you could have more than one null value. Also remember null never equals null. Of course it could do in real life but database theory over the years (not just relational) has said you can't normally assume it - so don't. (Incidentally I think, though I've not tested it, you can't use null valueA <> null valueB either)

This may not sound helpful but I suspect if you change your approach to thinking how you are going to identify individual records, that might help.

I admit I can't follow what you are trying to do but the fact that you are allowing a partial match suggests your database is not normalised. If you normalised it you might find you don't need to try and identify bunches of records. You will be able to update one record in a table which then applies to/references your desired bunch in the other table, automatically. mike.stephens@bnpparibas.com

 

[pjm]

Hi Smurfer

If you are desperate then you could join on nz([table1].[MaybeNullField], &quot;NULL&quot = nz ([table2].[MaybeNullField],&quot;NULL&quot

It's horrid, but it works...

pjm

 

[BNPMike]

Does it work?

The documentation says Nz is only for variants and you can't store variants in Access tables can you? mike.stephens@bnpparibas.com

 

[Craig0201]

Smurfer,

Think you're referring to outer joins. Try looking it up on the internet as my explantion would be horrid!

Craig

 

[smurfer]

PJM,
thank you very much...that was exactly what I needed...
by the way to bnpmike, I know the term semi-primary key was strange but it was the only way I could explain this db. Please note I did not create this db, I have just been asked to create an update query to update mass records while &quot;not&quot; changing the db structure at all. But thank you both for your assistance, and PJM I am grateful....
Thanks,
Smurfer