SQL UPDATE Issue

[Joe K.]

I have two tables: NM_Members and CA_Members
Both tables ID and Location fields.

NM_Members.ID is a primary key and CA_Members foreign key and the join is
set for ALL records from NM_Member and only those records from CA_Members
where the joined are equal. All records NM_Members are listed in the
CA_Member table.

When I execute the UPDATE listed below zero records are updated with the
data from the [CA_Members].Location field.

Please help me resolve this issue.

Thanks,

UPDATE CA_Members
INNER JOIN [NM_Members] ON [NM_Members].[ID]=[CA_Members].[ID]
SET [CA_Members].Location = [NM_Members].[Location]
WITH OWNERACCESS OPTION;

 

[John Spencer]

What results do you get if you run a SELECT query?

SELECT CA_Members.ID, CA_Members.Location, NM_Members.Location
FROM CA_Members INNER JOIN [NM_Members]
ON [NM_Members].[ID]=[CA_Members].[ID]

Does that show the records you want to update and the data to update the
location?

Did you actually execute your query or did you simply switch to datasheet
view? If you switched to datasheet view, you did not execute the query, you
are only seeing the records that WOULD BE updated if you executed the query
(Query: Run from the menu).

Your posted query in not a LEFT JOIN (what you described) but is an INNER
JOIN (only records where both tables have matching ID)

UPDATE CA_Members INNER JOIN [NM_Members]
ON [NM_Members].[ID]=[CA_Members].[ID]
SET [CA_Members].Location = [NM_Members].[Location]
WITH OWNERACCESS OPTION;

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..