Select distinct e-mail address from 1 table with 2 columns/e-mail to 1 column/e-mail

[NielsM]

Hello all,

I need some more help.....have a table 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)

In a previous posting I got some help which returned the SQL query
below...

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

This returns all unique contacts with one e-mail address who:
- want to receive e-mail = NoMassMailing = 0
- have an e-mail address (so filter out the empty and '@' values)
- if they have both Business and Private e-mail address only use the
Business e-mail address
- if they have only one e-mail address (Business OR Private) use this
address

Leaving only 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



Followed me so far? Good, because now I only want to see the unique
e-mail address.

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. .... I think has something to do with
First/Last, but I can't figure it out anymore.

Thanks already,
Niels