Get distinct info from fields 1

[evergreean]

For my Access 2000 database I am trying to fetch unique emails from two fields in one table.

Currently I have this in the table:

Name    OfficeEmail          HomeEmail
Jones   Jones@company.com    Jones@company.com
Baker   Baker@company.com    JBaker@earthlink.net
Dawson  Dawson@company.com    
Cooper  Cooper@company.com   TCooper@yahoo.com
Smith   Smith@company.com    Smith@company.com

I need distinct output like this:
Name Email
Jones Jones@company.com
Baker Baker@company.com
Baker JBaker@earthlink.net
Dawson Dawson@company.com
Cooper Cooper@company.com
Cooper TCooper@yahoo.com
Smith Smith@company.com

My attempt is not working:

select disinct officeEmail, name from tableOne
UNION ALL
select distinct homeEmail, name from tableOne;

 

[jrbarnett]

Try this:

select name, officeemail as EmailAddress
from yourtable
union
select name, homeemail
from yourtable
where homeemail is not null
order by name, EmailAddress

John

 

[evergreean]

Thanks it works.