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 bob#bob.com bob#priv.com 0
2 Jim Last last#jim.com jim#mail.com -1
3 Marc Johnson info#compy.com info#compy.com 0
4 Nicole Someone # info#compy.com 0
5 Hans Others hans#others.com 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 bob#bob.com
3 Marc Johnson info#compy.com
5 Hans Others hans#others.com

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
address

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


PEB
Expert 100+
P: 1,418
PEB
Hi,

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

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

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

:)
Oct 8 '06 #3

P: 2
Hi PEB,

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 bob#bob.com
3 Marc Johnson info#compy.com
4 Nicole Someone # info#compy.com
5 Hans Others hans#others.com

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 ?

Thanks,
Niels
Oct 8 '06 #4

PEB
Expert 100+
P: 1,418
PEB
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

Vladimir
Oct 14 '06 #5

Post your reply

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