Delete multiple records based on field value 1

[Wes1961]

I want to be able to delete all records for a sequence number when all the status for that seq are "Closed, Complete or Cancelled" in any combination. In the data below I would want to delete Seq #2 and #3.


Seq Job Status
1 A1 Open
1 B Closed
1 c Closed

2 D Closed
2 E4 Closed
2 B Closed

3 H Complete
3 I Closed
3 A Cancelled

4 H1 Open
4 M Open
4 N UnReleased

Can I do this in a query(s) or do I have to use VBA?

Can someone help me with this?

Thanks.

 

[RickSpr]

I think this query will do it.
DELETE FROM MyTable
WHERE Seq NOT IN (
SELECT Seq FROM MyTable
WHERE Status NOT IN ("Closed", "Complete", "Cancelled"));

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[Elysium]

Now that's some nice SQL.

Randy

 

[Wes1961]

Thanks much Rick!