update query only works partially

[Guest]

I'm stumped
I made a select query which selects records from table A that have a
matching value in another table B (using one field in each table). 300
records are returnd. If I change it to an update query, only 250 records in
table A get updated. All 300 fields in in table B (source table) have valid
values, and the field types are compatible.
Any thoughts?

 

[Guest]

Post the SQL and we can take a look.

 

[Guest]

UPDATE AA_Stabl, Farm SET AA_Stabl.Facility_ID = Farm.FarmID
WHERE (((Farm.[Facility Name]) Like [AA_Stabl].[STABLE_NAM]));

Thanks Dale

 

[John W. Vinson]

UPDATE AA_Stabl, Farm SET AA_Stabl.Facility_ID = Farm.FarmID
WHERE (((Farm.[Facility Name]) Like [AA_Stabl].[STABLE_NAM]));

Why are you using LIKE? That's only intended to be used with wildcard values,
which you're not using. And since you're joining on names, are you SURE that
the names are spelled, punctuated, and spaced *exactly the same* in both
tables? If [Facility Name] is "Mount Royal Ranch" and [STABLE_NAM] is "Mt.
Royal Ranch" then they won't match!

I'm rather surprised that this query runs at all, frankly, since you're using
an older SQL-88 WHERE clause join. I'd suggest (depending on the answer to the
first question) using

UPDATE AA_Stabl
INNER JOIN Farm
ON AA_Stabl.STABLE_NAM = Farm.[Facility Name]
SET AA_Stabl.[Facility_ID] = Farm.FarmID;

John W. Vinson [MVP]

 

[John Spencer]

So if one record in table a matches 3 records in table b you get 3 records
returned for the one record in table a.

If you update table a then you only update one record for the 3 records in
B.

If you update a field in table a (other than the matching field) with a
value from table B, ONE of the values will be assigned to the table a field
and the other two will be ignored. Actually it may be that all three
updates occur and the "last" update is the one that is retained. I really
don't know what the underlying logic is.

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