Removing duplicates

[toosh27]

Hi

I'm trying to run a query which will remove records that have a duplicated value in a field called "REFVAL". The query uses only one table, called "tblTemp" and writes the results to another table called "tbltemp1". I'm new to SQL and I keep getting a syntax error.

The error is "You tried to execute a query that does not include the specified expression 'CPUSE' as part of an aggregate function"

The SQL code is "SELECT tblTemp.CPUSE, tblTemp.REFVAL, tblTemp.TRADEAS, tblTemp.ADDRESS, tblTemp.LASTINSP, tblTemp.NEXTINSD, tblTemp.CLOSEDD, tblTemp.EHINSTYPE INTO tblTemp1 FROM tblTemp GROUP BY tblTemp.REFVAL HAVING Count(*) > 1;"

Any help would be very much appreciated
Simon

 

[mresann]

Rather than use a query that has aggregate functions, create a sub-query which identifies the field with the duplicate records, then use that as the basis for exclusion in the main query.

SELECT tblTemp.REFVAL, tblTemp.CPUSE, tblTemp.TRADEAS, tblTemp.ADDRESS, tblTemp.LASTINSP, tblTemp.NEXTINSD, tblTemp.CLOSEDD, tblTemp.EHINSTYPE
FROM tblTemp
WHERE (((tblTemp.REFVAL) Not In (SELECT [REFVAL] FROM [tblTemp] As Tmp GROUP BY [REFVAL] HAVING Count(*)>1 )))
ORDER BY tblTemp.REFVAL;

 

[toosh27]

Hi. Thanks for this but its now deleting the duplicated record as well as it's duplicates. I need it to keep one of them and delete the others. e.g if there are 18 records which are all duplicates then I want to keep one of them and delete the other 17. Any ideas? Thanks Simon

 

[modest]

I don't know if this thread is still alive, but why don't you just make a new temp table, append DISTINCT records there, clear your original table, and then fill it back up (or rename your temp table).

or... just append the records that have duplicates to the temp table... use the query above to delete all the duplicates, and then insert all the DISTINCT records from your temp table to your original table (this might be more time/resource efficient).


- modest