Piggy,
Is there a Primary Key on the table? Some way to uniquely identify a
record like a unique constraint?
Otherwise how does the db 'know' which of the same records to delete?
Given you have a PK make 2 queries
SELECT Min(CDT2.PK) AS MinOfPK, CDT2.Code, CLng([DateTime]) AS DateOf
FROM CodeDateTime AS CDT2
WHERE (((CDT2.DateTime)=(SELECT Max(CDT3.DateTime) FROM CodeDateTime as
CDT3 WHERE clng(CDT3.DateTime) = clng(CDT2.DateTime))))
GROUP BY CDT2.Code, CLng([DateTime]);
I've called it qselPKSToKeep. This will 'arbitarily' select one of the
PK's too keep if the datetime and code are same (the lowest number but it
could be the max, it doesn't matter)
Second Query (the 'outer' query)
DELETE CodeDateTime.PK, CodeDateTime.DateTime, CodeDateTime.Code
FROM CodeDateTime
WHERE (((CodeDateTime.PK) Not In (SELECT PTK.MinOfPK FROM qselPKSToKeep
as PTK)));
There may be a simpler way but I certainly can't think of it now....
Peter
ia*******@yahoo.com (The Pig) wrote in
news:81**************************@posting.google.c om:
Got a table.
Date Time Code
1/1/2003 1700 xbc
1/1/2003 1800 xbc
1/1/2003 1800 xbc
2/4/2004 1650 abc
2/4/2004 1700 abc
The problem is I need a query that will delete rows that have the same
"Code" and same "date" but will keep the line that has the higher
value in in the time field. Sometimes there are rows that have one
time that is lower and one that is higher, sometimes there can be 20
lines that have different times but the same higher value (ie. first
three lines of sample table above). I'm lost and have been banging my
head for the past two days. The finish table would looke like this.
Date Time Code
1/1/2003 1800 xbc
2/4/2004 1700 abc
Any help would be appreciated.
The Pig
8675309