|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
July 18th, 2008, 05:32 PM
posted to microsoft.public.access.queries
|
|||
|
|||
Delete Related records
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 
|
|
#2
July 18th, 2008, 06:08 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Delete Related records
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. -- KARL DEWEY Build a little - Test a little "stvlai" wrote: 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
|
|
#3
July 18th, 2008, 10:07 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Delete Related records
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 '================================================= === stvlai wrote: 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
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
