Select distinct e-mail adress from 2 columns

[NielsM]

Hello all,

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

ID : autonumber
Title : text
FirstName : text
LastName : text
OrganisationNam e : text
BusinessEmailAd dres : text (isNull, @ or valid e-mail address)
PrivateEmailAdd ress: 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

[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!

:)

[PEB]

IIF has the following Structure:

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

:)

[NielsM]

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.Fir stName, tblExport03.Las tName,

IIf([BusinessEmailAd dress] Is Null Or [BusinessEmailAd dress]="@",
IIf([PrivateEmailAdd ress] Is Null Or [PrivateEmailAdd ress]="@",Null,[PrivateEmailAdd ress]),[BusinessEmailAd dress]) AS EmailAddress

FROM tblExport03

WHERE (((IIf([BusinessEmailAd dress] Is Null Or [BusinessEmailAd dress]="@",

IIf([PrivateEmailAdd ress] Is Null Or [PrivateEmailAdd ress]="@",Null,[PrivateEmailAdd ress]),[BusinessEmailAd dress])) Is Not Null)
And ((tblExport03.N oMassMailing)=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

[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