delete certain rows

[subs]

Ocity Dcity equipmt carrier price note
chicago Atlanta c dash 1210 most current
chicago Atlanta c dash 2310
chicago atlanta d dash 1231
houston chicago d dash 5490 most current


I have a table like the above one. i need to delete the rows where
ocity,dcity,equipmt and carrier fields are equal but
the note field does not have the value "most current"

for example i will delete the 2nd row and keep the rest in the table
how to do this by a SQL query
pls help

 

[John Spencer]

Is note a text field? What other values might it contain besides blank and
"most current"?

Do you wish to delete the records permanently or just not see them in the
query result?

Do your records have a primary key of some sort?

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.

DELETE
FROM [SomeTable] as A
WHERE Exists
(SELECT *
FROM [SomeTable] as B
WHERE B.OCity = A.Ocity
AND B.DCity = A.DCity
AND B.Equipmt = A.Equipmt
AND A.Carrier = B.Carrier
AND B.Note = "most current")
AND A.Note & "" <> "most current"

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County