Don't know, but there is bad bracketing on the where clause in the subquery.
WHERE [Temp.ACCOUNTNUMBER] = [Find duplicates for Tbl-CCMain].ACCOUNTNUMBER);
should be
WHERE [Temp].[ACCOUNTNUMBER] = [Find duplicates for Tbl-CCMain].ACCOUNTNUMBER);
So the query should read as follows.
DELETE *
FROM [Find duplicates for Tbl-CCMain]
WHERE [Find duplicates for Tbl-CCMain].ARREARMONTH<
(SELECT Max([Find duplicates for Tbl-CCMain].ARREARMONTH)
FROM [Find duplicates for Tbl-CCMain] as Temp
WHERE [Temp].[ACCOUNTNUMBER]=[Find duplicates for Tbl-CCMain].ACCOUNTNUMBER);
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
Hi John,
I've tried this query but 0 row of records to be deleted (i expect 3 records)
DELETE *
FROM [Find duplicates for Tbl-CCMain]
WHERE [Find duplicates for Tbl-CCMain].ARREARMONTH<
(SELECT Max([Find duplicates for Tbl-CCMain].ARREARMONTH)
FROM [Find duplicates for Tbl-CCMain] as Temp
WHERE [Temp.ACCOUNTNUMBER] = [Find duplicates for Tbl-CCMain].ACCOUNTNUMBER);
John Spencer said:
STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.
Without a backup you cannot restore the data if this does not work the way you
expect.
I would try a query that looked like the following.
DELETE
FROM SomeTable
WHERE mthInArrears <
(SELECT Max(mthInArrears)
FROM SomeTable as Temp
WHERE Temp.AcNo = SomeTable.Acno
You might be able to substitute the find duplicates query for SomeTable in the
above. That could be faster or it could be slower - experiment.
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
zyus wrote:
Hi,
Appreciate if you could advise me on my problems as follow
In my Find Duplicates query i'm able to extract duplicate info on the acno
field as per below sample
ref acno mthinarrears
a 12345 5
b 12345 2
c 54321 2
d 54321 1
e 78945 6
f 78945 2
I want to delete duplicate record where the mthinarrears is lower than the
previous data. As a result i will maintain this record.
ref acno mthinarrears
a 12345 5
c 54321 2
e 78945 6
How to do this in a delete query or any other method.
:
You don't have to delete the records, you can use a select query to return
the desired records
SELECT TableName.ID, Max(TableName.DateFieldName) AS MaxOfDate
FROM TableName
GROUP BY TableName.ID
============================
If you still want to delete the records
First back up your data.
Try
DELETE TableName.*
FROM TableName
WHERE TableName.DateFieldName<>DMax("[DateFieldName]","[TableName]","[ID] ="
& [ID])
--
\\// Live Long and Prosper \\//
BS"D
:
I have a large number of records that are duplicated on the IDfield, but have
unique dates in a date field. I want to keep the record with the oldest date
and delete the duplicates that have later dates. For ex:
ID Date
01 1/1/2000
01 12/31/2002
01 3/5/2004
02 2/2/1999
02 3/1/1999
====And I would like to delete everything except the below, so that I have a
unique value in the ID field and the oldest date.
ID Date
01 1/1/2000
02 2/2/1999
I have several thousand records like this, so obviously I'd like to find a
way to automate this. Any help is greatly appreciated.
