How is Access performing this update?

[dan08]

I have two table tblA and tblB. tblB was made by selecting distinct First and Last name from tblA.
So tblB has 500 rows and tblA has 800 rows. Now I want to add the Country of birth to tblB with an update query joining tblA and tblB on first and last name.
Already you can see the conflict, there might be multiple values in tblA destined for one row in tblB.

When I run the update Access tells me I am about to update 800 rows, but really it is only updating the 500 rows in tblB.

What is really going on under the hood here, and what is a better way to do this operation?

 

[dhookom]

If tblB has a unique index on First and Last name, you should be able to use a value from tblB to update tblA but not the other way. You could use DLookup() or DMax() to update tblB from values in tblA.

Duane
Hook'D on Access
MS Access MVP

 

[lameid]

Dan08,

Access is updating the same rows more than once. So if John Smith is in the US and UK, after the update, tblB will have one of those countries but not both as technically one happens first. I never experimented with which you get as knowing this, I either don't do it or only do it when I know the column I am updating from is the same for all instances of the fields joined. So if all John Smith's live in the US, then it doesn't matter.

As to what is better... I have know idea as it depends on what your data actually is and what your desired output should be. My first inclination would be to make tblB again but including the country...
Duane is right in that there should be some sort of Index/key field so you can relate these tables appropriately.

Other than that, I would make a query to look at the group results for the name and country combo... Then Join that aggregate query to tblB in another query, again grouping but only on the name and counting country. Any having a count >1 are cases where there is more than one country. Possibly there are none.

 

[dan08]

Let me preface this by saying that the point of this excercise is to fix a databse with horrible data, and the keys are useless.

I didn't include the country because I wanted to get the uniwue individuals.

When I went and checked the value that was updated I saw what lameid was talking about. A value was assigned but it was 1 of multiple values from the original table, and who knows why the value was chosen? Anyway I reconstructed the query (using several queries), to incrementaly update subsets, using the best possible data.