combine two columns in one new table

[brief4niels]

Hallo All,

I have the following table with fields/columns and test data) called
tblExport03

ID FirstName LastName BusinessEmail PrivateEmail NoMail
1 Bob Bobson (e-mail address removed) (e-mail address removed) o
2 Jim Last (e-mail address removed) (e-mail address removed) x
3 Marc Johnson (e-mail address removed) (e-mail address removed) o
4 Nicole Someone @ (e-mail address removed) o
5 Hans Others (e-mail address removed) o
6 Tanja Big @ @ o

I want to select all persons who want e-mail (NoMail =o) and only have
one column with e-mail addresses... If a person has both a business and
private e-mail address then only use the business email address. If
there is no business e-mail address use the private e-mail address
(this is where it goes wrong)

This is the data I would like to see
ID FirstName LastName EmailAddress
1 Bob Bobson (e-mail address removed)
3 Marc Johnson (e-mail address removed)
4 Nicole Someone (e-mail address removed)
5 Hans Others (e-mail address removed)

Below is the SQL satement I have so far.... problem is that it doesn't
add the private e-mail address in to the EmailAddress column when there
is no or '@' BusinessEmail

SELECT ID, FirstName, LastName, BusinessEmailAddress AS EmailAddress
FROM tblExport03
WHERE (NoMassMailing=0) AND ((BusinessEmailAddress<>'@') OR(
(IsNull(BusinessEmailAddress) OR (BusinessEmailAddress='@') AND
(PrivateEmailAddress<>'@' ))))
UNION SELECT ID, FirstName, LastName, PrivateEmailAddress AS
EmailAddress
FROM tblExport03
WHERE (NoMassMailing=0) AND IsNull(BusinessEmailAddress) AND
(BusinessEmailAddress=PrivateEmailAddress)

I used the 'AS EmailAddress' twice which is probably the problem...

This is the data I get from the above SQL statement
ID FirstName LastName EmailAddress
1 Bob Bobson (e-mail address removed)
3 Marc Johnson (e-mail address removed)
4 Nicole Someone @
5 Hans Others

Thanks for your help,
Niels

 

[Guest]

Is the field named NoMassMailing or NoMass? Does it contain a letter “O†or
a 0 (zero)? I used NoMail and "O".

Try this --
SELECT tblExport03.ID, tblExport03.FirstName, tblExport03.LastName,
IIf([BusinessEmail] Is Null Or [BusinessEmail]="@",IIf([PrivateEmail] Is Null
Or [PrivateEmail]="@",Null,[PrivateEmail]),[BusinessEmail]) AS [E-mail],
tblExport03.NoMail
FROM tblExport03
WHERE (((IIf([BusinessEmail] Is Null Or
[BusinessEmail]="@",IIf([PrivateEmail] Is Null Or
[PrivateEmail]="@",Null,[PrivateEmail]),[BusinessEmail])) Is Not Null) AND
((tblExport03.NoMail)="o"));

 

[Dale Fye]

I'm going to make the assumption (unlike KARL) that you really don't have
the @ symbol in the "empty" Business or Private email fields. It these
fields are NULL by default, then you should be able to use the following.

SELECT ID, FirstName, LastName, NZ([BusinessEmail],[PrivateEmail]) AS
EmailAddress
FROM tblExport03
WHERE (NZ([BusinessEmail],[PrivateEmail]) Is Not Null) AND
(tblExport03.NoMail)="o");

HTH
Dale

 

[brief4niels]

Hello Guys,

Thanks for your help.... it works !!

I've changed the fieldnames so my table design fits more or less as
text on your screen. The actual table/field names are: (real name -
used here in this post)
BusinessEmailAddress - BusinessEmail
PrivateEmailAddress - PrivateEmail
NoMassMailing - NoMass = which is a checkbox and therefore has the
value's 0 (zero) or -1

Dale: Both Private and Business Email Address field can contain 1) a
real email adres 2) empty/nothing or 3) the default value '@'

Working query (had to rewrite some fieldnames and changed "0" to 0)

SELECT tblExport03.ID, tblExport03.FirstName, tblExport03.LastName,
IIf([BusinessEmailAddress] Is Null Or
[BusinessEmailAddress]="@",IIf([PrivateEmailAddress] Is Null
Or
[PrivateEmailAddress]="@",Null,[PrivateEmailAddress]),[BusinessEmailAddress])
AS [EmailAddress], tblExport03.NoMassMailing
FROM tblExport03
WHERE (((IIf([BusinessEmailAddress] Is Null Or
[BusinessEmailAddress]="@",IIf([PrivateEmailAddress] Is Null Or
[PrivateEmailAddress]="@",Null,[PrivateEmailAddress]),[BusinessEmailAddress]))
Is Not Null) AND
((tblExport03.NoMassMailing)=0));


Thanks again,
Niels

 

[brief4niels]

Hardly dare to ask,
but how can I alter this query so I only get each e-mail address only
once

So I get these records
ID FirstName LastName EmailAddress
1 Bob Bobson (e-mail address removed)
4 Nicole Someone (e-mail address removed)
5 Hans Others (e-mail address removed)

Both ID 3 and 4 have the same e-mail address, and for that matter I
don't care which one is selected....
I think has something to do with First/Last, but I can't figure it out
anymore.

Thanks again,
Niels