SQL Syntax

[Joe]

Hi,
i have these 2 tables called "Members" and "info." i would
like to transfer the information in from the field "name"
in "members" to the "name" field in the table "info" only
where i have a box checked. i have prepared the following
SQL statement to be run in a macro:

UPDATE [Info] SET [Info].NAME =[Members].NAME
FROM [Members]
WHERE (((Members.ResponseCheckBox)=Yes));

i keep getting a syntax error saying "missing operators"
i would greatly appreciate it if someone could proofread
this for syntax. Thanks a lot everyone!

-joe

 

[John Vinson]

Hi,
i have these 2 tables called "Members" and "info." i would
like to transfer the information in from the field "name"
in "members" to the "name" field in the table "info" only
where i have a box checked. i have prepared the following
SQL statement to be run in a macro:

UPDATE [Info] SET [Info].NAME =[Members].NAME
FROM [Members]
WHERE (((Members.ResponseCheckBox)=Yes));

i keep getting a syntax error saying "missing operators"
i would greatly appreciate it if someone could proofread
this for syntax. Thanks a lot everyone!

You will need to use a Join betewen the two tables; right now you're
not referencing the Info table at all in the FROM clause, so Access
has no idea what table you're using, nor which record in that table.

I really have to be concerned about the table design here! Storing the
Name redundantly in two different tables is almost surely bad design;
normally one would store only a unique MemberID in the Info table as a
link to the name information in [Members]. In addition, the word NAME
is a reserved term, and it's best not to use it as a fieldname -
MemberName perhaps, or even better, LastName and FirstName as separate
fields.

But to directly answer the question:

UPDATE Info INNER JOIN Members
ON Info.ForeignKeyField = Members.PrimaryKeyField
SET Info.[Name] = Members.[Name]
WHERE Members.[ResponseCheckBox];

will work, if you change PrimaryKeyField and ForeignKeyField to the
names or the Primary Key in Members, and the name of the field to
which that is related in Info. If you do not have a primary key or a
relationship... well, you need a primary key and you need a
relationship!