Delete query based on criteria HELP!

[Cam]

Hello,

I couldn’t figure out how to create a delete query to delete records from an
appended table based on a criteria.
As the table is being appended one a day, it will have the following fields
with the same data of Order#, Part#, Oper#, Qty. It also has a field called
DIA, which is number of days order seat in the same operation.

The criteria to delete the records would be if any record has the same
Order#, Part# and Oper#, delete those records except the one with the highest
number in DIA field. Below is the example data:

Order# Part# Oper# Qty DIA AppendDt
420 80722-1 310 4 1 13-Oct-09
420 80722-1 310 4 7 23-Oct-09
826 4560-9 320 2 1 19-Oct-09
826 4560-9 320 2 2 21-Oct-09
826 4560-9 320 2 3 23-Oct-09
829 4560-9 301 2 2 24-Oct-09
995 4560-9 210 2 1 20-Oct-09
995 4560-9 210 2 4 23-Oct-09
217 7115-1 150 24 14 23-Oct-09

DELETE:
Order# Part# Oper# Qty DIA AppendDt
420 80722-1 310 4 1 13-Oct-09
826 4560-9 320 2 1 19-Oct-09
826 4560-9 320 2 2 21-Oct-09
995 4560-9 210 2 1 20-Oct-09

KEEP:
Order# Part# Oper# Qty DIA AppendDt
420 80722-1 310 4 7 23-Oct-09
826 4560-9 320 2 3 23-Oct-09
829 4560-9 301 2 2 24-Oct-09
995 4560-9 210 2 4 23-Oct-09
217 7115-1 150 24 14 23-Oct-09

Thanks for any help.

 

[Ken Snell]

Try this:

DELETE *
FROM YourTableName
WHERE DIA <>
(SELECT Max(T.DIA) AS MDIA
FROM YourTableName AS T
WHERE T.[Order#] = YourTableName.[Order#]
AND T.[Part#] = YourTableName.[Part#] AND
T.[Oper#] = YourTableName.[Oper#]);

 

[Cam]

Ken,

it worked. Thank you.

Try this:

DELETE *
FROM YourTableName
WHERE DIA <>
(SELECT Max(T.DIA) AS MDIA
FROM YourTableName AS T
WHERE T.[Order#] = YourTableName.[Order#]
AND T.[Part#] = YourTableName.[Part#] AND
T.[Oper#] = YourTableName.[Oper#]);

--

Ken Snell
http://www.accessmvp.com/KDSnell/

Hello,

I couldn't figure out how to create a delete query to delete records from
an
appended table based on a criteria.
As the table is being appended one a day, it will have the following
fields
with the same data of Order#, Part#, Oper#, Qty. It also has a field
called
DIA, which is number of days order seat in the same operation.

The criteria to delete the records would be if any record has the same
Order#, Part# and Oper#, delete those records except the one with the
highest
number in DIA field. Below is the example data:

Order# Part# Oper# Qty DIA AppendDt
420 80722-1 310 4 1 13-Oct-09
420 80722-1 310 4 7 23-Oct-09
826 4560-9 320 2 1 19-Oct-09
826 4560-9 320 2 2 21-Oct-09
826 4560-9 320 2 3 23-Oct-09
829 4560-9 301 2 2 24-Oct-09
995 4560-9 210 2 1 20-Oct-09
995 4560-9 210 2 4 23-Oct-09
217 7115-1 150 24 14 23-Oct-09

DELETE:
Order# Part# Oper# Qty DIA AppendDt
420 80722-1 310 4 1 13-Oct-09
826 4560-9 320 2 1 19-Oct-09
826 4560-9 320 2 2 21-Oct-09
995 4560-9 210 2 1 20-Oct-09

KEEP:
Order# Part# Oper# Qty DIA AppendDt
420 80722-1 310 4 7 23-Oct-09
826 4560-9 320 2 3 23-Oct-09
829 4560-9 301 2 2 24-Oct-09
995 4560-9 210 2 4 23-Oct-09
217 7115-1 150 24 14 23-Oct-09

Thanks for any help.

.