I have a linked table (tblProofCycle) that I'd like to run through a query to create a new table.

The table contains the following fields: Order, Desc, dtDate, dtHour, dtMin. The new table (tblProofs) contains the same fields.

Each activity of an order is recorded in the linked table. I'd like to delete all instances of an order if one of it's Description values is "Customer".

Here's an example:

Order DESC DTDATE DTHOUR DTMINUTE
101243769 6/1/2010 6 29
101243769 Customer 6/1/2010 8 1
101243769 6/1/2010 8 1
101243770 6/1/2010 6 32
101243773 6/1/2010 6 35

Using the above example, I need to remove all records for Order 101243769 because one of them contains "Customer".

Can this be done with a delete query?

Thanks.

that will delete any record where DESC is "Customer"

if you want to delete any record that contains "Customer", you can use Like




hth (test on a backup!!! )

Nevermind... I hate when I reply before I fully understand the question


I think this may be done using a subquery, but unfortunately I don't know how off the type of my head and am not currently in a position to test and find out. Sorry for the bogus reply...

I would use something like the following. Since ORDER and DESC are reserved words you will probably be forced to surround each field name with square brackets.


STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the way you expect.

DELETE *
FROM tblProofCycle
WHERE [Order] IN
(SELECT [ORDER]
FROM tblProofCycle
WHERE [DESC] = "Customer")

That seems to have worked!

Thanks.