Delete Related records

[stvlai]

Hi

I have a Table that have 4 Fields : ID, Serial_No, Status, Model_No.
Contents of my Table

ID Serial_No Status Model_No
1 11 Ship AAA
2 11 Ready AAA
3 11 Repair AAA
4 22 Receive BBB
5 22 Repair BBB
6 33 Ship CCC
7 33 Note CCC

How can I delete all the Serial_No Records that have "Ship" status?
So, in this case only the Record with Serial No "22" in ID 4 & 5 will
remain. Since SerialNo 11 & 33 have "Ship" Status, all the related
records to "22" & "33" will be deleted. Record ID 1,2,3,6,7 deleted.

Final Table :
ID Serial_No Status Model_No
4 22 Receive BBB
5 22 Repair BBB

If this can;t be done, can I query all records with "Shipped" to
another Table "B" and use the Table "B" as a reference to delete all
those related Serial No in the Master table?

Any advise is much appreciated. Thanks a bunch!

SLKW

 

[KARL DEWEY]

Create a totals query with [Status] = "Ship", outputing [Serial_No].
Use it in another query joined on [Serial_No].

I would recommend not deleting but adding a flag field like 'Archived' or
'Old' so that the historical data will still be available. Just have
criteria in your queries to not pull archived/old records.

 

[John Spencer]

IF I understand correctly, use a subquery to identify any serial_no that
has a status equal to ship to identify the serial_No that need to be
deleted.

SQL would be something like the following:

DELETE
FROM YourTable
WHERE Serial_No in
(SELECT Serial_No
FROM YourTable
WHERE Status = "Ship")

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