Re: [MS_AccessPros] Update Multiple Values in one query

 

But here's a safe way to do it - it won't update unless ALL the Mail address are Null:

UPDATE tblAddresses

SET 

[Mail Addr1 Ld] = [Home Addr1 Ld],

[Mail Addr2 Ld] = [Home Addr2 Ld], 

[Mail Addr3 Ld] = [Home Addr3 Ld], 

[Mail Addr4 Ld] = [Home Addr4 Ld], 

[Mail City Nm] = [Home City Nm], 

[Mail Postal Cd] = [Home Postal Cd], 

[Mail  State Cd] = [Home State Cd]

WHERE 

[Mail Addr1 Ld] IS NULL AND

[Mail Addr2 Ld] IS NULL AND

[Mail Addr3 Ld] IS NULL AND

[Mail Addr4 Ld] IS NULL AND

[Mail City Nm] IS NULL AND

[Mail Postal Cd] IS NULL AND

[Mail  State Cd] IS NULL;

John Viescas, Author

Effective SQL

SQL Queries for Mere Mortals 

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications 

http://www.viescas.com/ 

(Paris, France)

On Sep 22, 2016, at 2:17 AM, 'Graham Mandeno' graham@mandeno.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Hi Jim

This is a dangerous approach, because it can end up with meaningless junk in your mailing address fields.  For example, if your mailing address used only two of the AddrX fields, but your home address uses all four, you don't want MailAddr3 and MailAddr4 to be populated from HomeAddr3 and HomeAddr4!

It also creates a maintenance problem, because if the home address changes, how do you know whether or not to change the mail address?

A better approach would be to add a boolean field, DifferentMailAddress.  On your data entry form you can hide the mail address fields if that checkbox is not selected, and when you are generating mailing address labels etc, you can use IIf expressions in your query – for example:

IIf( [DifferentMailAddress], [Mail Addr1 Ld], [Home Addr1 Ld] ) AS [Addr1 Ld]

Best wishes,

Graham Mandeno [Access MVP 1996-2016]

 

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] 
Sent: Thursday, 22 September 2016 09:32
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Update Multiple Values in one query

Hello all,

I have a table that has address fields. There are two sets of addresses which are Home and Mailing. Sort of like a business and a home address. 

Because there are nulls in one the sets of addresses I want to put the values from the Home addresses into the Mailing address fields if the Mailing address fields are null. 

Right now I have the following sql and it works but if I add the other fields nothing happens. 

How do I write the query to update all of the fields in one query?

here are the fields

Home Addr1 Ld

Home Addr2 Ld

Home Addr3 Ld

Home Addr4 Ld

Home City Nm

Home Postal Cd

Home State Cd

Mail Addr1 Ld

Mail Addr2 Ld

Mail Addr3 Ld

Mail Addr4 Ld

Mail City Nm

Mail Postal Cd

Mail  State Cd

 

UPDATE tblEmployeeAddresses SET tblEmployeeAddresses.[Mail Addr1 Ld] = [Home Addr1 Ld]
WHERE (((tblEmployeeAddresses.[Mail Addr1 Ld])="-"));

 

Thank You

Jim Wagner

__._,_.___

---

Posted by: John Viescas <johnv@msn.com>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (3)

---

Have you tried the highest rated email app?

With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

---

Visit Your Group

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___