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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|