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
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
__._,_.___
Posted by: Duane Hookom <duanehookom@hotmail.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (4) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar