A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Update Query



 
 
Thread Tools Display Modes
  #1  
Old April 28th, 2010, 09:29 PM posted to microsoft.public.access.queries
Glenn Dulmage
external usenet poster
 
Posts: 12
Default Update Query

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


  #2  
Old April 28th, 2010, 10:51 PM posted to microsoft.public.access.queries
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default Update Query

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" wrote in message
...
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



  #3  
Old April 29th, 2010, 01:06 AM posted to microsoft.public.access.queries
Glenn Dulmage
external usenet poster
 
Posts: 12
Default Update Query

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

Glenn

"Jeff Boyce" wrote in message
...
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" wrote in message
...
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



  #4  
Old April 29th, 2010, 01:50 AM posted to microsoft.public.access.queries
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Update Query

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]
  #5  
Old April 29th, 2010, 04:17 PM posted to microsoft.public.access.queries
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default Update Query

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" wrote in message
...
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



  #6  
Old April 30th, 2010, 03:26 PM posted to microsoft.public.access.queries
Glenn Dulmage
external usenet poster
 
Posts: 12
Default Update Query

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" wrote in message
...
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" wrote in message
...
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



  #7  
Old April 30th, 2010, 06:19 PM posted to microsoft.public.access.queries
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default Update Query

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.

"Glenn Dulmage" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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





 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 01:26 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.