asked on

Access 2007 - query syntax

My OS is windows 7 and I use Access 2007. I have a database which has duplicates and I created a find duplicates from the query wizard. The result was all emails that were more than 1. I now want to delete those that are more than 1, but when I try to run the same query using the Delete feature, looks like it is deleting ALL the emails in the Table. The query syntax that I created is in the code section. Thank u

SELECT First(Contacts.[E-mail Address]) AS [E-mail Address Field], Count(Contacts.[E-mail Address]) AS NumberOfDups
FROM Contacts
GROUP BY Contacts.[E-mail Address]
HAVING (((Count(Contacts.[E-mail Address]))>1));

Open in new window

Gerwin Jansen

Hi, this will select the (first) duplicate id's:

SELECT First(Contacts.[ID]) AS [E-mail Address Field]
FROM Contacts
GROUP BY Contacts.[E-mail Address]
HAVING (((Count(Contacts.[E-mail Address]))>1));

Open in new window

To delete them:

delete from contacts
where contacts.id
in
(
  SELECT First(Contacts.[ID]) AS [E-mail Address Field]
  FROM Contacts
  GROUP BY Contacts.[E-mail Address]
  HAVING (((Count(Contacts.[E-mail Address]))>1))
);

Open in new window

Jegajothy vythilingam

ASKER

In response to gerwinjansen, I ran your code, and it deleted but not all. I created again and inserted your code and it reduced the number of finds which I deleted and I ran again the third time and it reduced to about 11 and deleted again.
Cannot understand why it just did not delete everything in the first go.

ASKER CERTIFIED SOLUTION

Gerwin Jansen

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jegajothy vythilingam

ASKER

In response to gerwinjansen, thank u for your response. When I search there will be more than 1 record. Say it displays 3 records, thus I would like to delete the 2 records and leaving the 1 record in the db. My apologies for not being clearer. thank u.