ACCESS FEED: RE: [MS_AccessPros] Removing Duplicate entries

Minggu, 19 Oktober 2014

RE: [MS_AccessPros] Removing Duplicate entries

Hi Robin

Yes – Duane is quite right. Since FieldID is unique, your subquery will return no records, as there are no cases where there is more than one record having the save FieldID. Therefore, no records will be deleted.

You need to GROUP BY all the other fields except FieldID – or at least sufficient fields to identify the records as duplicates.

Also, the query will delete only one of the duplicates in each set every time it is run – the one with the smallest FieldID value. If you have three or more duplicates of the same record you will need to run the query multiple times.

Best wishes,
Graham

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Monday, 20 October 2014 16:49
To: Access Professionals Yahoo Group
Subject: RE: [MS_AccessPros] Removing Duplicate entries

If your field [FieldID] is an autonumber primary key then there will be no records deleted. What is returned from this query:

SELECT MIN(FieldID) AS MinOfFieldID
FROM [Students]
Group By [fieldid]
Having Count(fieldID) > 1

Based on your description, this will not return any records that could be deleted. You would need to define "duplicate entries".

Duane Hookom MVP
MS Access

To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Mon, 20 Oct 2014 09:52:26 +1100
Subject: [MS_AccessPros] Removing Duplicate entries

I have a table with a few duplicate entries.

The Microsoft forum page says:

""" Make sure that your table has an autonumber field and it is set as the primary key. I'll call it TempID. """

My table has 'FieldID' as an autonumber field and is the primary key.

The page says:

DELETE <yourtable>.* FROM <yourtable> WHERE TempID IN(SELECT MIN(TempID) AS MinOfTempID FROM <yourtable> Group By [Name] Having Count(TempID) > 1)

I have edited to this:

DELETE <students>.* FROM <students> WHERE FieldD IN(SELECT MIN(FieldID) AS MinOfFieldID FROM <students> Group By [fieldid] Having Count(fieldID) > 1)

and I still get an error. (The marker appears at the second <students> above.)

What Have I missed?

Many thanks,

Robin Chapple