On Wed, 28 Apr 2010 16:29:01 -0400, "Glenn Dulmage"
wrote:

I have 2 tables: Member and Bulk Mail
I have linked them on Last Name
Member has these fields: First Name, Last Name, E-mail
Bulk mail has these fields: First Name, Last Name, e-mail address

The member table has no data in the E-mail field
The Bulk Mail table has data in the e-mail address field

I would like to update the Member E-mail field with the data in the Bulk
Mail e-mail address field using the Query Wizard

Jeff's point is well taken; if you insist on using this very limited database
you'll probably have to reject the next highly qualified job applicant because
he or she happens to have a name that would cause a duplicate.

In addition, storing the firstname, lastname, and email field redudnantly in
both table seems really pointless. If Jane Roberts gets married and becomes
Jane Zimmerman, or Dave Simpson changes his name to Ahmad al-Hadjii, you'll
have to update both tables. Why not store the data once and look it up, as is
normal in relational databases?

That said, put a unique Index on the Last Name field in both tables. Create a
Query joining the two tables on Last Name. Select the Member E-mail field and
put a criterion of IS NULL, and the bulk mail E-Mail field and put a criterion
of IS NOT NULL. This query will pull all records where there is an email
address to be transferred. Change the query to an Update query and type

[Bulk Mail].[E-Mail]

on the Update To line under the Member EMail field. Run the query by clicking
the ! icon.
--

John W. Vinson [MVP]