By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,705 Members | 1,777 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,705 IT Pros & Developers. It's quick & easy.

Select distinct e-mail adress from 2 columns

P: 2
Hello all,

I have a table (tblExport03) with the following fields:

ID : autonumber
Title : text
FirstName : text
LastName : text
OrganisationName : text
BusinessEmailAddres : text (isNull, @ or valid e-mail address)
PrivateEmailAddress: text (isNull, @ or valid e-mail address)
NoMassMailing : checkbox (-1 = on = don't want to receive e-mail)

Some test data (abbreviated the column names)
ID FirstName LastName BusinessEmail PrivateEmail NoMass..
1 Bob Bobson 0
2 Jim Last -1
3 Marc Johnson 0
4 Nicole Someone # 0
5 Hans Others 0
6 Tanja Big # # 0

(# = the 'at' sign)

My problem is that there are 2 columns with e-mail addresses that need to be combined into one column and then only select unique e-mail addresses.

So I get these records
ID FirstName LastName EmailAddress
1 Bob Bobson
3 Marc Johnson
5 Hans Others

Both ID 3 and 4 have the same e-mail address, and for that matter I
don't care which one is selected.

There are some more restraints:
- if NoMassMailing = 0 then don't select person
- use only valid e-mail addresses (so filter out the empty and '@' values)
- if person has both Business and Private e-mail address only use the
Business e-mail address
- if person has only one e-mail address (Business OR Private) use this

Thanks already,
Oct 8 '06 #1
Share this Question
Share on Google+
4 Replies

Expert 100+
P: 1,418

To combine your fields you need the function IIF()

An exemple like:

MyNewColumn: IIF(Isnull([Mail1]),[Mail2],[Mail1])

And you'll obtain all mails in 1 column with priority of Mail1

It's up to you to fit those things in your query!

Oct 8 '06 #2

Expert 100+
P: 1,418
IIF has the following Structure:

IIF(Condition, When condition is true, When Condition is False)

Oct 8 '06 #3

P: 2

Thanks for your reply and explanation.

At the moment I have my constraints figured out and get all e-mail addresses in one column. Problem is the e-mail addresses aren't unique.

This is what I have now:
SELECT tblExport03.ID, tblExport03.FirstName, tblExport03.LastName,

IIf([BusinessEmailAddress] Is Null Or [BusinessEmailAddress]="@",
IIf([PrivateEmailAddress] Is Null Or [PrivateEmailAddress]="@",Null,[PrivateEmailAddress]),[BusinessEmailAddress]) AS EmailAddress

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))

Giving me these records
ID FirstName LastName EmailAddress
1 Bob Bobson
3 Marc Johnson
4 Nicole Someone #
5 Hans Others

Records with ID 3 and 4 share have the same e-mail address... how do I select only one of these? Can I put a DISTINCT in an IIF Condition ?

Oct 8 '06 #4

Expert 100+
P: 1,418
Hi Niels,

You can group By / Distinct only on the column with IIF The other columns don't need to participate in your query. Is this idea is good for your task?

Best regards

Oct 14 '06 #5

Post your reply

Sign in to post your reply or Sign up for a free account.