Delete Multiple Lines

[Guest]

One Table, One Query - Want to delete multiple lines from table with criteria
from query:

Table Fields: Serial Number, Date Shipped, Qty Shipped, Qty Avail
1234 1/1/2006 100 400
1234 2/1/2006 200 200
1234 3/1/2006 200 0

Query Fields: Serial Number = 1234, Min of Qty Avail = 0

Need to delete all line items for serial number with Min Qty Avail = 0

Multiple Queries the way to go? Having trouble getting it to work.

 

[Guest]

Sounds like a simple Delete query. Delete from [table name] where [qty
avail] = 0.

Yes?

 

[John Spencer]

So you want to delete all records that have the Serial Number 1234 since one
of the records has a qty avail of zero. If that is correct

DELETE DistinctRow T.*
FROM [TableName] as T
WHERE T.[Serial Number] In (
SELECT T1.[Serial Number]
FROM [TableName] as T1
WHERE T1.[qty avail] = 0
)

If [qty avail] is not zero in any record but is less than zero in a record
and you want to delete all the matching serial number records just change
WHERE T1.[qty avail] = 0
to
WHERE T1.[qty avail] < 1

If you have negative numbers in qty avail for a serial number do you want to
delete the records with that serial number or keep all the records since the
minimum is not zero but a number less than zero? That can also be done by
using

WHERE T.[Serial Number] In (
SELECT T1.[Serial Number]
FROM [TableName] as T1
GROUP BY [Serial Number]
Having Min(T1.[qty avail] )= 0
)