My Access Delete query gets the error "Specify the table containing the records you want to delete"

Question

I don’t know what is wrong with this Delete Query(Reconcilement Query6(TEST)). I want to delete the records in tblTemporaryTranactions that narrowed down to the records left in Reconcilement Query5

The SQl View is:

DELETE tblTemporaryTransactions.ID, tblTemporaryTransactions.Account, tblTemporaryTransactions.Debit, tblTemporaryTransactions.Credit, tblTemporaryTransactions.Balance
FROM tblTemporaryTransactions INNER JOIN [Reconcilement Query5] ON tblTemporaryTransactions.ID = [Reconcilement Query5].ID;

Any help would be appreciated. Bob

Answer 1

Try:

DELETE *
FROM tblTemporaryTransactions
WHERE EXISTS
(SELECT *
FROM [Reconcilement Query5]
WHERE [Reconcilement Query5].ID = tblTemporaryTransactions.ID);

Note: there should be a space and asterisk after DELETE and SELECT in the above statement. The formatting here seems to remove them.

Answer 2

Hi Bob,

Just curious, would this work as well?

DELETE DISTINCTROW FROM tblTemporaryTransactions
INNER JOIN [Reconcilement Query5]
ON tblTemporaryTransactions.ID=[Reconcilement Query5].ID

Could you please test and let us know? Thanks!

Answer 3

Sorry thedbguy but it produces the error "Syntax error in DELETE statement". Bob

Answer 4

Did my original SQL statement, with the addition of the missing asterisks, work? The line numbers must be omitted of course:

DELETE *
FROM tblTemporaryTransactions
WHERE EXISTS
    (SELECT *
     FROM [Reconcilement Query5]
     WHERE [Reconcilement Query5].ID = tblTemporaryTransactions.ID);