Delete query

[Simon Harris]

I'm using Access2000 and I've got a problem with a delete query which
I'm *sure* I had working before )-: I have a table (logfile) which I
import from an external source that has a load of stuff I don't need.
So I have another table (tests_not_reqd) that lists the tests I don't
need. Link 'em together with an inner join to see the tests in
'logfile' that I don't need - all looking good so far ...
SELECT Logfile.*
FROM tests_not_reqd INNER JOIN Logfile ON tests_not_reqd.test_code =
Logfile.[Test Code];

When I change it to a 'delete' query ...
DELETE Logfile.*
FROM tests_not_reqd INNER JOIN Logfile ON tests_not_reqd.test_code =
Logfile.[Test Code];

.... it gives an error 'could not delete from specified tables'. I'm
guessing it's something really simple I'm doing wrong - can anyone
help?

Simon Harris

[Dave M]

You need to use a sub-query for this:

DELETE Logfile.*
FROM tests_not_reqd
WHERE test_code In (SELECT [Test Code] FROM Logfile)
"Simon Harris" <si**********@kingshc.nhs.uk> wrote in message
news:d8**************************@posting.google.c om...

I'm using Access2000 and I've got a problem with a delete query which
I'm *sure* I had working before )-: I have a table (logfile) which I
import from an external source that has a load of stuff I don't need.
So I have another table (tests_not_reqd) that lists the tests I don't
need. Link 'em together with an inner join to see the tests in
'logfile' that I don't need - all looking good so far ...
SELECT Logfile.*
FROM tests_not_reqd INNER JOIN Logfile ON tests_not_reqd.test_code =
Logfile.[Test Code];

When I change it to a 'delete' query ...
DELETE Logfile.*
FROM tests_not_reqd INNER JOIN Logfile ON tests_not_reqd.test_code =
Logfile.[Test Code];

... it gives an error 'could not delete from specified tables'. I'm
guessing it's something really simple I'm doing wrong - can anyone
help?

Simon Harris