473,321 Members | 1,669 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,321 software developers and data experts.

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
Nov 13 '05 #1
1 2635
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

Nov 13 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Andrew DeFaria | last post by:
I created the following .sql file to demonstrate a problem I'm having. According to the manual: If |ON DELETE CASCADE| is specified, and a row in the parent table is deleted, then InnoDB...
2
by: Joe Del Medico | last post by:
I have two tables A & B and I want to delete all the records in A that are not in B. Can I do this in the query builder? It seems like a simple problem. I can easily find the records in A that...
5
by: ms | last post by:
Why does this select query return the correct records but when I make it a delete query I get a msgbox with "Could not delete from specified tables". SELECT BMIDLog.* FROM stageBMIDLog INNER...
2
by: Dave Burt | last post by:
Hi, Access officionados, I'm new here, so please cut me slack/gently tell me off if I'm out of line or in the wrong place. OK, here's something that seems silly (and is also problematic to...
8
by: John Baker | last post by:
Hi: Access 2000 W98! I have a table with numerous records in it, and am attempting to delete certain records that have been selected from it. These are selected based on the ID number in a...
13
by: forbes | last post by:
Hi, I have a user that used the Query Wizard to create a query in Access. Now she claims that her master table is missing all the data that was excluded from the query. Can you create anything...
3
by: Kevin M | last post by:
I have one table and have created a form from that table. Also, I have created a delete query. I want to but a button on the form which will delete all records from the table; however, I cannot get...
1
by: Matt | last post by:
I am writing a DELETE statement and I want to filter the records using another SELECT statement. My SELECT statement is a GROUP BY query that grabs all social security numbers from the "Data...
5
by: Bob Bridges | last post by:
Start with two tables, parent records in one and child records in the other, a one-to-many relationship. Create a select statement joining the two. Display the query in datasheet mode. When I...
3
by: Phil Stanton | last post by:
I have a form based on a complex query (Lots of tables) If I delete a record, everything appears to be OK. Get the message "Youa are about to delete 1 record ....". I say yes. The record count...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.