Update Query

[Glenn Dulmage]

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

Glenn

Glenn T. Dulmage
207 Valley Road
Chestertown, MD 21620
410-778-5166
(e-mail address removed)

 

[Jeff Boyce]

Glenn

So, you quite certain that you don't have and never will have two people
with the same last name?

If you link on [LastName], how do you discriminate between John Smith and
his brother, Joseph Smith?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Glenn Dulmage]

The member table is only 57 records and there are no duplicate last names in
the other table.

Glenn

Glenn

So, you quite certain that you don't have and never will have two people
with the same last name?

If you link on [LastName], how do you discriminate between John Smith and
his brother, Joseph Smith?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

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

Glenn

Glenn T. Dulmage
207 Valley Road
Chestertown, MD 21620
410-778-5166
(e-mail address removed)

 

[John W. Vinson]

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.

 

[Jeff Boyce]

To recap...

Yes, you can do that ... but I can drive nails with my chainsaw! Neither is
a very good use of the tool.

If you don't take the time to get the data in a structure that Access is
designed to work with, both you and Access will be continually working on
work-arounds... pay now or pay later!

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Glenn Dulmage]

My problem is that I inherited the database and the Table structure. What I
am tempted to do is put an ID field in the Member Table and search out the
57 members in the Bulk Mail table and then type in the matching ID and then
link ob that field.

Thanks for all the hep.

 

[Jeff Boyce]

Glenn

If you are only dealing with 57 rows, that sure sounds like a good approach.

I suspect you could spend a lot longer trying to come up with a work-around
.... for THIS issue, then still need a solution for the next issue <g>!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.