Union query to remove duplicates

[sudhirhinduja]

Hi all,

I have created an ExecutiveList table with 2 years of data 2003 and 2004.

But about 50% of them are duplicates. So I created 2 Find duplicate queries:

1)
SELECT ExecutiveList.Name, ExecutiveList.ExecID, ExecutiveList.Title, ExecutiveList.Company, ExecutiveList.Address1, ExecutiveList.Address2, ExecutiveList.City, ExecutiveList.State, ExecutiveList.ZIP, ExecutiveList.Country, ExecutiveList.Phone, ExecutiveList.Salary, ExecutiveList.Bonus, ExecutiveList.TotalCashComp, ExecutiveList.ProxDate, ExecutiveList.CompID
FROM ExecutiveList
WHERE (((ExecutiveList.Name) In (SELECT [Name] FROM [ExecutiveList] As Tmp GROUP BY [Name] HAVING Count(*)>1 )) AND ((ExecutiveList.ProxDate) Like "*2003*") AND ((ExecutiveList.CompID) In (SELECT [CompID] FROM [ExecutiveList] As Tmp GROUP BY [CompID] HAVING Count(*)>1 )))
ORDER BY ExecutiveList.Name;

2)

SELECT ExecutiveList.Name, ExecutiveList.ExecID, ExecutiveList.Title, ExecutiveList.Company, ExecutiveList.Address1, ExecutiveList.Address2, ExecutiveList.City, ExecutiveList.State, ExecutiveList.ZIP, ExecutiveList.Country, ExecutiveList.Phone, ExecutiveList.Salary, ExecutiveList.Bonus, ExecutiveList.TotalCashComp, ExecutiveList.ProxDate, ExecutiveList.CompID
FROM ExecutiveList
WHERE (((ExecutiveList.Name) In (SELECT [Name] FROM [ExecutiveList] As Tmp GROUP BY [Name] HAVING Count(*)>1 )) AND ((ExecutiveList.ProxDate) Like "*2004*") AND ((ExecutiveList.CompID) In (SELECT [CompID] FROM [ExecutiveList] As Tmp GROUP BY [CompID] HAVING Count(*)>1 )))
ORDER BY ExecutiveList.Name;

I want to fetch only the latest executives when duplicates occur and retain the ones which do not repeat in either year.

I think I need to use the union query on the ExecutiveList.Name field. Can anyone help me on how to create one?

Thanks,