combine two columns in one new table

  • Thread starter Thread starter brief4niels
  • Start date Start date
B

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
 
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"));
 
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
 
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
 
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
 
Back
Top