?
Solved

if field is null then refer to a different field

Posted on 2007-10-04
2
Medium Priority
?
659 Views
Last Modified: 2012-06-21
I need to create a make table query for custoemr adress.  if customer Bill to Adress is null, then I want the query to refer to customer ship to adress. PLs give me the correct SQL

SELECT
a.ID,
a.NAME,
a.ADDR_1,
a.ADDR_2,
a.ADDR_3,
a.CITY,
a.ZIPCODE,
a.COUNTRY,
a.BILL_TO_ADDR_1,
a.BILL_TO_ADDR_2,
a.BILL_TO_ADDR_3,
a.BILL_TO_CITY,
a.BILL_TO_STATE,
a.BILL_TO_ZIPCODE,
a.BILL_TO_COUNTRY
FROM SYSADM_CUSTOMER AS a;

I want:
a.ADDR_1,
a.ADDR_2,
a.ADDR_3,
a.CITY,
a.ZIPCODE,
 a.COUNTRY
 to replace

a.BILL_TO_ADDR_1,
a.BILL_TO_ADDR_2,
a.BILL_TO_ADDR_3,
a.BILL_TO_CITY,
a.BILL_TO_STATE,
a.BILL_TO_ZIPCODE,
a.BILL_TO_COUNTRY

if a.BILL_TO_ADDR_1 IS NULL

UPDATE SYSADM_CUSTOMER AS a SET
a.ADDR_1=BILL_TO_ADDR_1,
a.ADDR_2=BILL_TO_ADDR_2,
a.ADDR_3=BILL_TO_ADDR_3,
a.CITY=a.BILL_TO_CITY,
a.ZIPCODE=a.BILL_TO_ZIPCODE
a.COUNTRY=a.BILL_TO_COUNTRY;

0
Comment
Question by:gringotani
2 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20019619
do you want to UPDATE the table effectively, or just query for the relevant data?

query: in access, use the NZ() function:

SELECT ....

 , BILL_TO_ADDR1 = NZ ( a.BILL_TO_ADDR_1, A.ADDR1 )
 , BILL_TO_ADDR2 = NZ ( a.BILL_TO_ADDR_2, A.ADDR2 )
 ... etc

for update, you can use the same formula
0
 
LVL 77

Accepted Solution

by:
peter57r earned 500 total points
ID: 20020720
I'm not quite sure whether you are wanting a make-table query or an Update query.
And it's also not clear to me whether the Bill-to address is either completely present or completely absent, or whether you can have bits of a bill-to address present.

I shall assume it is either completely present or completely absent.

For a make-table query you would have:

SELECT
a.ID,
a.NAME,
a.ADDR_1,
a.ADDR_2,
a.ADDR_3,
a.CITY,
a.ZIPCODE,
a.COUNTRY,
nz(a.BILL_TO_ADDR_1, a.Addr_1) as Bill_to_Addr1,
nz(a.BILL_TO_ADDR_2, a.Addr_2) as Bill_to_Addr2,
nz(a.BILL_TO_ADDR_3, a.Addr_3) as Bill_to_Addr3,
nz(a.BILL_TO_CITY, a.City) as City,
nz(a.BILL_TO_STATE, A.State) as State,
nz(a.BILL_TO_ZIPCODE, A.zipcode) as zipcode,
nz(a.BILL_TO_COUNTRY, A.country) as Country,

Into newtablename
FROM SYSADM_CUSTOMER AS a;
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses
Course of the Month15 days, 13 hours left to enroll

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question