If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Select distinct e-mail address from 1 table with 2 columns/e-mail to 1 column/e-mail
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 |
Thread Tools | |
Display Modes | |
|
|