Update Query Help

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have the following query. Several of the fields will have either a blank
RecordLocatorMain.DeskCode or RecordLocatorMain.RCDLOC. I need the
combination of the two to be the same, even if one is blank, but my query
will only update those records where neither of those two fields is
blank/null. Can someone tell me why and how I can fix this? Thanks much -

UPDATE RecordLocatorMain INNER JOIN RecordLocatorNew ON
(RecordLocatorMain.DeskCode = RecordLocatorNew.DeskCode) AND
(RecordLocatorMain.RCDLOC = RecordLocatorNew.RCDLOC) SET
RecordLocatorMain.TeamLeader = [RecordLocatorNew].[TeamLeader],
RecordLocatorMain.Buyer = [RecordLocatorNew].[Buyer],
RecordLocatorMain.ProcurementMgr = [RecordLocatorNew].[ProcurementMgr];
 
It seems from your wording (I may be misinterpreting) that, even if one of
the JOIN conditions is not satisfied, you still want this update to occur.
The AND in the JOIN condition requires both of the join conditions to be
satisfied. Maybe you want an OR?

You may also want to use the Nz() function to provide a non-NULL value for
the comparisons in the event that one of the fields is null. For example,
(assuming these are text fields):
Nz(RLM.DeskCode, "None") = Nz(RLN.DeskCode, "None")

(Aliasing used for formatting purposes.) The Nz() will cause the string
"None" to be used for the test, for any field that is NULL. If these are
numerics, you might use -1 or some other value that cannot possibly be a
valid DeskCode instead.

I admit that I am skating on thin ice here as I'm not completely confident
that I understand what you want to happen.

Good Luck!
 
Thanks. I'm not trying to ask that the update occur if one of the JOIN
conditions isn't satisfied. If RLM.deskcode = 84 and RLM.recordloc = null
and RLN.deskcode = 84 and RLN.recordloc = null, then aren't these two records
the same and satisfied? This is where I'm confused, but I understand that
the null value is messing this up.

I'll try using nz, but I'm not sure where to add this to the SQL in in the
design view of my update query. Can you help?

Thanks

Chaim said:
It seems from your wording (I may be misinterpreting) that, even if one of
the JOIN conditions is not satisfied, you still want this update to occur.
The AND in the JOIN condition requires both of the join conditions to be
satisfied. Maybe you want an OR?

You may also want to use the Nz() function to provide a non-NULL value for
the comparisons in the event that one of the fields is null. For example,
(assuming these are text fields):
Nz(RLM.DeskCode, "None") = Nz(RLN.DeskCode, "None")

(Aliasing used for formatting purposes.) The Nz() will cause the string
"None" to be used for the test, for any field that is NULL. If these are
numerics, you might use -1 or some other value that cannot possibly be a
valid DeskCode instead.

I admit that I am skating on thin ice here as I'm not completely confident
that I understand what you want to happen.

Good Luck!
--
Chaim


Alex said:
I have the following query. Several of the fields will have either a blank
RecordLocatorMain.DeskCode or RecordLocatorMain.RCDLOC. I need the
combination of the two to be the same, even if one is blank, but my query
will only update those records where neither of those two fields is
blank/null. Can someone tell me why and how I can fix this? Thanks much -

UPDATE RecordLocatorMain INNER JOIN RecordLocatorNew ON
(RecordLocatorMain.DeskCode = RecordLocatorNew.DeskCode) AND
(RecordLocatorMain.RCDLOC = RecordLocatorNew.RCDLOC) SET
RecordLocatorMain.TeamLeader = [RecordLocatorNew].[TeamLeader],
RecordLocatorMain.Buyer = [RecordLocatorNew].[Buyer],
RecordLocatorMain.ProcurementMgr = [RecordLocatorNew].[ProcurementMgr];
Click to expand...
Click to expand...
 
First of all, NULL represents an "I don't know" value. Therefore, NULL is NOT
equal to NULL. You don't know whether it is or not.

You have to add the Nz() in SQL View, since when I tried this, Access
complained that it couldn't represent the query in Design View, and it listed
several reasons. What you'll have to do is open the query in Design View,
right click and select SQL View to see the text of your Append Query. I guess
you've done this already to get the SQL in your post. In SQL View, simply
edit the text so that the ON clause of the JOIN looks like:

ON ( Nz (RLM.DeskCode, "None") = Nz (RLN.DeskCode, "None") )
AND ( Nx (RLM.RCDLOC, "None") = Nz (RLN.RCDLOC, "None") )

You won't be able to switch back to Design View, although the query is valid
and will work.

Good Luck!
--
Chaim


Alex said:
Thanks. I'm not trying to ask that the update occur if one of the JOIN
conditions isn't satisfied. If RLM.deskcode = 84 and RLM.recordloc = null
and RLN.deskcode = 84 and RLN.recordloc = null, then aren't these two records
the same and satisfied? This is where I'm confused, but I understand that
the null value is messing this up.

I'll try using nz, but I'm not sure where to add this to the SQL in in the
design view of my update query. Can you help?

Thanks

Chaim said:
It seems from your wording (I may be misinterpreting) that, even if one of
the JOIN conditions is not satisfied, you still want this update to occur.
The AND in the JOIN condition requires both of the join conditions to be
satisfied. Maybe you want an OR?

You may also want to use the Nz() function to provide a non-NULL value for
the comparisons in the event that one of the fields is null. For example,
(assuming these are text fields):
Nz(RLM.DeskCode, "None") = Nz(RLN.DeskCode, "None")

(Aliasing used for formatting purposes.) The Nz() will cause the string
"None" to be used for the test, for any field that is NULL. If these are
numerics, you might use -1 or some other value that cannot possibly be a
valid DeskCode instead.

I admit that I am skating on thin ice here as I'm not completely confident
that I understand what you want to happen.

Good Luck!
--
Chaim


Alex said:
I have the following query. Several of the fields will have either a blank
RecordLocatorMain.DeskCode or RecordLocatorMain.RCDLOC. I need the
combination of the two to be the same, even if one is blank, but my query
will only update those records where neither of those two fields is
blank/null. Can someone tell me why and how I can fix this? Thanks much -

UPDATE RecordLocatorMain INNER JOIN RecordLocatorNew ON
(RecordLocatorMain.DeskCode = RecordLocatorNew.DeskCode) AND
(RecordLocatorMain.RCDLOC = RecordLocatorNew.RCDLOC) SET
RecordLocatorMain.TeamLeader = [RecordLocatorNew].[TeamLeader],
RecordLocatorMain.Buyer = [RecordLocatorNew].[Buyer],
RecordLocatorMain.ProcurementMgr = [RecordLocatorNew].[ProcurementMgr];
Click to expand...
Click to expand...
Click to expand...
 
Perfect explanation - thank you. I'm just learning SQL and understand that
it is much powerful than using the Design View. Thank you so much for your
help.

Chaim said:
First of all, NULL represents an "I don't know" value. Therefore, NULL is NOT
equal to NULL. You don't know whether it is or not.

You have to add the Nz() in SQL View, since when I tried this, Access
complained that it couldn't represent the query in Design View, and it listed
several reasons. What you'll have to do is open the query in Design View,
right click and select SQL View to see the text of your Append Query. I guess
you've done this already to get the SQL in your post. In SQL View, simply
edit the text so that the ON clause of the JOIN looks like:

ON ( Nz (RLM.DeskCode, "None") = Nz (RLN.DeskCode, "None") )
AND ( Nx (RLM.RCDLOC, "None") = Nz (RLN.RCDLOC, "None") )

You won't be able to switch back to Design View, although the query is valid
and will work.

Good Luck!
--
Chaim


Alex said:
Thanks. I'm not trying to ask that the update occur if one of the JOIN
conditions isn't satisfied. If RLM.deskcode = 84 and RLM.recordloc = null
and RLN.deskcode = 84 and RLN.recordloc = null, then aren't these two records
the same and satisfied? This is where I'm confused, but I understand that
the null value is messing this up.

I'll try using nz, but I'm not sure where to add this to the SQL in in the
design view of my update query. Can you help?

Thanks

Chaim said:
It seems from your wording (I may be misinterpreting) that, even if one of
the JOIN conditions is not satisfied, you still want this update to occur.
The AND in the JOIN condition requires both of the join conditions to be
satisfied. Maybe you want an OR?

You may also want to use the Nz() function to provide a non-NULL value for
the comparisons in the event that one of the fields is null. For example,
(assuming these are text fields):
Nz(RLM.DeskCode, "None") = Nz(RLN.DeskCode, "None")

(Aliasing used for formatting purposes.) The Nz() will cause the string
"None" to be used for the test, for any field that is NULL. If these are
numerics, you might use -1 or some other value that cannot possibly be a
valid DeskCode instead.

I admit that I am skating on thin ice here as I'm not completely confident
that I understand what you want to happen.

Good Luck!
--
Chaim


:

I have the following query. Several of the fields will have either a blank
RecordLocatorMain.DeskCode or RecordLocatorMain.RCDLOC. I need the
combination of the two to be the same, even if one is blank, but my query
will only update those records where neither of those two fields is
blank/null. Can someone tell me why and how I can fix this? Thanks much -

UPDATE RecordLocatorMain INNER JOIN RecordLocatorNew ON
(RecordLocatorMain.DeskCode = RecordLocatorNew.DeskCode) AND
(RecordLocatorMain.RCDLOC = RecordLocatorNew.RCDLOC) SET
RecordLocatorMain.TeamLeader = [RecordLocatorNew].[TeamLeader],
RecordLocatorMain.Buyer = [RecordLocatorNew].[Buyer],
RecordLocatorMain.ProcurementMgr = [RecordLocatorNew].[ProcurementMgr];
Click to expand...
Click to expand...
Click to expand...
 
Back
Top