Delete Max value

[Trudye]

Hi Guys, I am trying to add a Delete statement to this query to Delete all dupes based on MAX(tblOdomStatement.fldDateStamp.

I cannot get an adequate response. Any ideas?

SELECT tblOdomStatement.fldVin, tblOdomStatement.fldLeaseNum, tblOdomStatement.fldDateStamp, tblOdomStatement.fldCustomerSign, tblOdomStatement.fldDealerSign, tblOdomStatement.fldGroundDate
FROM tblOdomStatement
WHERE (((tblOdomStatement.fldVin) In (SELECT [fldVin] FROM [tblOdomStatement] As Tmp GROUP BY [fldVin],[fldLeaseNum] HAVING Count(*)>1 And [fldLeaseNum] = [tblOdomStatement].[fldLeaseNum])))
ORDER BY tblOdomStatement.fldVin, tblOdomStatement.fldLeaseNum;

Thanx
Trudye

 

[Ronze55]

I would do a count on an entire record then insert the entire record into a new table without the count field. This would then be your new table.

You could then delete, preferably rename, your table with dups. and rename your new table to that. overhead but it is safer.

since we are in the access forum, i guess you are using ms access. create a macro to control this process.

If you enter a delete statement you will delete all the records not just the extra records.

Hope this helps
ronze

 

[Trudye]

Ok I decided to do it on two passes. However I am having trouble with the delete. I am getting the msg the a simicolon is missing. I tried rearranging the parens but to no avail.

Here is my code:
DELETE *
FROM _sig_tblOdomStmnt
WHERE (([_sig_tblOdomStmnt].fldVin) In (SELECT [fldVin] FROM [_sig_tblOdomStmnt] As Tmp GROUP BY [fldVin],[LeaseNum] HAVING Count(*)>1 And [LeaseNum] = [_sig_tblOdomStmnt].[LeaseNum])
ORDER BY [_sig_tblOdomStmnt].fldVin, [_sig_tblOdomStmnt].LeaseNum);

Thanx
Trudye

 

[Crowley16]

your brackets are wrong...

--------------------
Procrastinate Now!