Delete query

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

re: Delete query

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" <simon.harris@kingshc.nhs.uk> wrote in message
news:d827adc7.0412060800.5fe8fdb2@posting.google.c om...[color=blue]
> 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[/color]