My Issue is that as a result of an append query I get duplicate records. The
Duplicate record has an auto number so I only want to delete the oldest
record which would be the min Value ,but I cannot get the query to work . Any
suggestions

Thanks
--
Nadine

nadine wrote:
> My Issue is that as a result of an append query I get duplicate records.

First make a backup of your table in case something goes wrong. You must
next determine which columns identify *duplicates* and place the primary key
or a unique index (disallowing nulls) on this combination of columns, but
*after* you remove these duplicates. In this example, ID is the autonumber
and all duplicate records with the same FirstName and LastName are removed,
leaving only the most recent records:

DELETE *
FROM TableA
WHERE (ID NOT IN
(SELECT MAX(ID) AS MaxID
FROM TableA
GROUP BY FirstName, LastName
HAVING COUNT(*) > 1)) AND
(ID NOT IN
(SELECT MIN(ID) AS MinID
FROM TableA
GROUP BY FirstName, LastName
HAVING COUNT(*) = 1));

After running this query the primary key should be placed on the LastName and
FirstName columns to prevent further duplicates.

--
Message posted via http://www.accessmonster.com

This solved my problem also. Thanks
--
DimmickT


"Granny Spitz via AccessMonster.com" wrote:

> nadine wrote:
> > My Issue is that as a result of an append query I get duplicate records.
>
> First make a backup of your table in case something goes wrong. You must
> next determine which columns identify *duplicates* and place the primary key
> or a unique index (disallowing nulls) on this combination of columns, but
> *after* you remove these duplicates. In this example, ID is the autonumber
> and all duplicate records with the same FirstName and LastName are removed,
> leaving only the most recent records:
>
> DELETE *
> FROM TableA
> WHERE (ID NOT IN
> (SELECT MAX(ID) AS MaxID
> FROM TableA
> GROUP BY FirstName, LastName
> HAVING COUNT(*) > 1)) AND
> (ID NOT IN
> (SELECT MIN(ID) AS MinID
> FROM TableA
> GROUP BY FirstName, LastName
> HAVING COUNT(*) = 1));
>
> After running this query the primary key should be placed on the LastName and
> FirstName columns to prevent further duplicates.
>
> --
> Message posted via http://www.accessmonster.com
>
>

"DimmickT" <(E-Mail Removed)> wrote in message
news:603067B1-1589-4A46-83CD-(E-Mail Removed)...
> This solved my problem also. Thanks
> --
> DimmickT
>
>
> "Granny Spitz via AccessMonster.com" wrote:
>
>> nadine wrote:
>> > My Issue is that as a result of an append query I get duplicate
>> > records.
>>
>> First make a backup of your table in case something goes wrong. You must
>> next determine which columns identify *duplicates* and place the primary
>> key
>> or a unique index (disallowing nulls) on this combination of columns, but
>> *after* you remove these duplicates. In this example, ID is the
>> autonumber
>> and all duplicate records with the same FirstName and LastName are
>> removed,
>> leaving only the most recent records:
>>
>> DELETE *
>> FROM TableA
>> WHERE (ID NOT IN
>> (SELECT MAX(ID) AS MaxID
>> FROM TableA
>> GROUP BY FirstName, LastName
>> HAVING COUNT(*) > 1)) AND
>> (ID NOT IN
>> (SELECT MIN(ID) AS MinID
>> FROM TableA
>> GROUP BY FirstName, LastName
>> HAVING COUNT(*) = 1));
>>
>> After running this query the primary key should be placed on the LastName
>> and
>> FirstName columns to prevent further duplicates.
>>
>> --
>> Message posted via http://www.accessmonster.com
>>
>>