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 Ready 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

Any advise is much appreciated. Thanks a bunch!

SLKW

 

[pietlinden]

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              Ready      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

Any advise is much appreciated. Thanks a bunch!

SLKW

DELETE
FROM MyTable
WHERE Status='Ship';

 

[tedmi]

....
DELETE
FROM MyTable
WHERE Status='Ship';

This would delete only the 2 records with status of SHIP, not the related
records with predecessor status. Try this:

DELETE FROM MyTable
WHERE Serial_No IN
(SELECT Serial_No FROM MyTable WHERE Status='Ship')

 

[a a r o n _ k e m p f]

Access doesn't really support cascading deletes / updates (because you
can't set up cross database RI)

I'd recommend moving to SQL Server, for sure.

 

[stvlai]

Hi All

Thanks for the suggestion. Really appreciate it. I try to use Ted's
suggestion.

DELETE FROM MyTable
WHERE Serial_No IN
(SELECT Serial_No FROM MyTable WHERE Status='Ship')

But I am getting this error "Query must have at least 1 destination
field".

If not, can I use q Query to create a Access table 'B' with all the
records with "Shipped" and then use table B as a reference to delete
all related records with similar Serial No in the Master Table?

Thanks for any suggestion. Have a nice weekend everyone.

STV

 

[stvlai]

Hi All

I think I found a solution. Just to share with anyone needing such
solution :

DELETE *
FROM Test_tbl_RMA_Range
WHERE Serial_No IN
(SELECT Serial_No FROM Test_tbl_RMA_Range WHERE
Test_tbl_RMA_Range.Action='Shipped');

Thanks a bunch to all trying to help! Helpful people! Make my day!

Have a nice weekend.

STV

 

[stvlai]

Hi

Actually, thanks to Ted's suggestions. Just added a * to the command.
It works!

STV