delete dup lot numbers unless qty is different

[rwn]

I have a query that deletes the most recent duplicate entry of a lot number for a table within a specific timeframe. I would like to modify the delete query so it still deletes the most recent entry of a duplicate, but ONLY if the lab table.qty is an exact match. So if a lot number is entered twice and a qty of 100 exists and 200 exists, for the same lot number, then no delete is perform. Any suggestions??


sSQL = "DELETE [Lab Table].TDate, [Lab Table].ID, * FROM [Lab Table] WHERE ((([Lab Table].TDate) Between Date()-5 And Date())" & _
" AND (([Lab Table].ID) Not In (SELECT Min(ID) as MinID FROM [Lab Table] WHERE ([Lab Table].TDate Between Date()-5 " & _
"And Date()) GROUP BY [Lab Table].LotNumber)));

 

[Crowley16]

use a subquery?

delete * from tbl where somecondition AND (
select qty from tbl2 where someCondition
) = tbl.qty

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

 

[rwn]

the sub query will run after the main delete query, so all dup will be gone already.

 

[rwn]

I added the code qty<>qty, but now no duplicates are deleted.

sSQL = "DELETE [Lab Table].TDate, [Lab Table].ID, * FROM [Lab Table] WHERE ((([Lab Table].TDate) Between Date()-5 And Date())" & _
" AND ([Lab Table].Qty <> [Lab Table].Qty)" & _
" AND (([Lab Table].ID) Not In (SELECT Min(ID) as MinID FROM [Lab Table] WHERE ([Lab Table].TDate Between Date()-5 " & _
"And Date()) GROUP BY [Lab Table].LotNumber)));"