Delete Query

E

enywu

Hi,

I'm trying to create an archive table here.
I have managed to copy those closed transaction to another table, bu
now I'm having problem to delete it from the original table.

the query as follows:

DELETE DISTINCTROW [Transaction - Headers].*
FROM [Transaction - Headers]
INNER JOIN [Transaction Headers - Closed PO] ON [Transaction
Headers].ref_no = [Transaction Headers - Closed PO].ref_no;

----------------
It keeps asking me to specify which table i want to delete.

Thank you very much in advance.

Regards,


En
 
J

John Spencer

I would check the first line and make sure you don't have an extra asterisk
* in the first line.
If that fails try

DELETE DISTINCTROW [Transaction - Headers].Ref_No
FROM [Transaction - Headers]
INNER JOIN [Transaction Headers - Closed PO]
ON [Transaction - Headers].ref_no = [Transaction Headers - Closed PO].ref_no

OR use a subquery in the where clause.

DELETE DISTINCTROW [Transaction - Headers].Ref_No
FROM [Transaction - Headers]
WHERE [Transaction - Headers].Ref_No IN
(SELECT [Transaction Headers - Closed PO].ref_no
FROM [Transaction Headers - Closed PO] )
 
J

Jeff Boyce

Why? You can "archive" a transaction without transferring it or removing it
from the original table.

If you add an "archive" field to the table (Yes/No if you only wish to know
THAT it was archived; Date/Time if you wish to know WHEN), you can modify
your queries to exclude "archived" records when you are displaying and
reporting on "active" records/transactions.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/
 
Top