another update query question

  • Thread starter Thread starter Jason Meyer
  • Start date Start date
J

Jason Meyer

OK I want to update a field of on table from the field of another when key
fields are the same....here is what I have but it doesn't update.....any
suggestions?


UPDATE Patrons, Rahsstu SET Patrons.teacher = Rahsstu!Field142
WHERE ((Patrons!pid=Rahsstu!Field8));

Thanks for the help.


Jason
 
Hi,

My name is Eric. Thank you for using the Microsoft Access Newsgroups.

You wrote:
"OK I want to update a field of on table from the field of another when key
fields are the same....here is what I have but it doesn't update.....any
suggestions?

UPDATE Patrons, Rahsstu SET Patrons.teacher = Rahsstu!Field142
WHERE ((Patrons!pid=Rahsstu!Field8)); "


Try:

UPDATE Patrons INNER JOIN Rahsstu ON Patrons.pid = Rahsstu.Field8 SET
Patrons.teacher = Rahsstu!Field142


I hope this helps! If you have additional questions on this topic, please
respond back to this posting.


Regards,

Eric Butts
Microsoft Access Support

"Microsoft Security Announcement: Have you installed the patch for
Microsoft Security Bulletin MS03-026? If not Microsoft strongly advises
you to review the information at the following link regarding Microsoft
Security Bulletin MS03-026
<http://www.microsoft.com/security/security_bulletins/ms03-026.asp> and/or
to visit Windows Update at <http://windowsupdate.microsoft.com/> to install
the patch. Running the SCAN program from the Windows Update site will help
to insure you are current with all security patches, not just MS03-026."
 
Thanks for the reply. But still no go, could I use REPLACE instead of UPDATE
to force the change?
 
Thanks for the reply. But still no go, could I use REPLACE instead of UPDATE
to force the change?
Click to expand...

REPLACE isn't valid SQL. I think the problem is that you're using !
(which Access uses for forms or other object references) rather than .
in the field name: try

UPDATE Patrons INNER JOIN Rahsstu ON Patrons.pid = Rahsstu.Field8 SET
Patrons.teacher = Rahsstu.Field142

This will also work only if pid is the Primary Key of Patrons, or if
it has a unique Index.
 
Well when I imported the two csv files I let it create a new primary key and
indexed them and it still says that there were 0 rows processed...even
though I manually changed the field.142 in the rahsstu table.
 
Back
Top