Update field with contents of another field

[JohnB]

I now know how to update a field, with text such as "record found", based on
the contents of another field.

I use something like this:
update tableb,tablea set tableb.blankfield = "found"
where tableb.stocknumber = tablea.stocknumber

But I need to do something a little different, and I can't figure out how to
do this:

I have a file with a link to a table in another file. And just as before, I
need to read the StockNumber field in one table and find matching records in
the linked table. When I find a match I need to update the contents of
Table-B.Location with the contents of Table-A.location

I tried, in place of "found", TableName.FieldName. but I get a message
saying Syntax error, missing operator.....
It's probably something simple, but since I've never done this before, I
don't know what the correct syntax would be.

TIA

 

[Michel Walsh]

UPDATE tableB INNER JOIN tableA
ON tableB.stockNumber = tableA.stockNumber
SET tableB.Location= tableA.Location
WHERE tableB.Location IS NULL


Sure, just in case it does not do what you want, preferable to experiment
AFTER having made a backup. An UPDATE can make extensive damage to your
data, if not properly done.

Vanderghast, Access MVP

 

[JohnB]

What is the purpose of the last line?
Is it stating "where" that field is blank? And if it isn't blank, do
nothing?

 

[Michel Walsh]

Indeed, for a given record, if the location is NOT NULL, that record WON'T
be updated.

It is 'as if' you first make a SELECT ... WHERE ... and, from the records
that are returned, UPDATE them (and only them).



Vanderghast, Access MVP

 

[JohnB]

I have a followup question:
I didn't explain the table and field names correctly, but I could figure it
out from what you wrote. This is your query with the corrected table/field
names:

UPDATE Products INNER JOIN All_Locations
ON Products.ProductStock=All_Locations.Stock_Number
SET Products.Actual_Location = All_Locations.Current_Veh_Location
WHERE Products.Actual_Location IS NULL;

But my problem is; I am getting prompted for information when I run the
query. I get a popup window titled "Enter Parameter Value".
With the input box labeled "All_Locations.Current_Veh_Location"

What is causing that prompt?

TIA

 

[Michel Walsh]

Probably a typo: the field is not recognized.

Vanderghast, Access MVP

 

[JohnB]

You were right, I did have a typo. Current_Veh_Location should have been
Current_Veh_Loc

But I corrected that... and I still get the same error message. I'm so
close!!

I'm Googling for answers, but everything I find points to typo errors. I've
looked very closely at the query and find none.

Any other recommendations would be greatly appreciated.

 

[Michel Walsh]

Have you tried to (re-)do it with the graphical query designer? at least,
that tool reduces some typos.

Vanderghast, Access MVP

 

[JohnB]

The challenge there is *how* to do that. I just started using Access
queries this week.
I guess I could look at the existing query in Design View, then delete it,
and recreate it based on the previous Design view. True?

*still lost in query land*

 

[JohnB]

Well, that worked.

This is the query that worked:
UPDATE Products INNER JOIN ALL_Locations
ON ALL_Locations.Stock_Number=Products.ProductStock
SET Products.Actual_Location = ALL_Locations![Current_Veh_ Loc];

This threw the error. The main difference that I see is in the last line,
it uses an exclamation mark instead of a period between the table and field.
(I didn't use the test for null, didn't care)
UPDATE Products INNER JOIN All_Locations
ON Products.ProductStock=All_Locations.Stock_Number
SET Products.Actual_Location = All_Locations.Current_Veh_Location