473,320 Members | 1,914 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,320 software developers and data experts.

Delete query problem...

I want to delete records from the "one" table of a one to may relationship.
There are no actual Access relationships set up. The "one" table has a
single field PK and the "many" table has a two field PK. This code works
when executed from a module (both DAO and ADO work), but not from the query
window.

DELETE a1.*, a2.Field1
FROM a1 LEFT JOIN a2 ON a1.Field1 = a2.Field1
WHERE (((a2.Field1) Is Null));

Why doesn't it work from the query window and how do I get ti to work?

Thanks.

Matthew Wells
MW****@NumberCruncher.com
Nov 12 '05 #1
1 1295
"Matthew Wells" <MW****@NumberCruncher.com> wrote in message
news:k6*****************@bignews3.bellsouth.net...
I want to delete records from the "one" table of a one to may relationship. There are no actual Access relationships set up. The "one" table has a
single field PK and the "many" table has a two field PK. This code works
when executed from a module (both DAO and ADO work), but not from the query window.

DELETE a1.*, a2.Field1
FROM a1 LEFT JOIN a2 ON a1.Field1 = a2.Field1
WHERE (((a2.Field1) Is Null));

Why doesn't it work from the query window and how do I get ti to work?

Thanks.

Matthew Wells
MW****@NumberCruncher.com

You could use a subquery - it makes it very clear from whichtable records
are being deleted from:

DELETE FROM a1 WHERE a1.Field1
NOT IN (SELECT a2.Field1 FROM a2)
Fletcher
Nov 12 '05 #2

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

Similar topics

2
by: r | last post by:
I am trying to do a delete query like this one: DELETE FROM pending WHERE pages.url=pending.url AND pages.date=pending.date; If I run it (via PHP) I get the following error: Unknown table...
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...
1
by: Mark Reed | last post by:
Hi All, I have a table with a date field and 6 number fields. The number fields are not in numerical order across the fields and I want number 1 to be the lowest number and number 6 to be the...
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...
11
by: Sheldon Glickler | last post by:
I have code that does not delete from a database. The same code (cut an paste in the same file, but different function and having a different query) works. So, of course, I tested the query...
4
by: Ian | last post by:
Hi, I have a problem with delete using where in clause. This is a query: delete from tab1 where id not in (select id from tab2) I calculated costs using select instead of delete: select...
7
by: alan_conoco | last post by:
Hi all, I am trying to delete a subset of records (identified using my InsertRemovePairs query) from a table names TradesDone. When I execute the nested SELECT on its own it returns the...
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: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
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: 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: 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...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

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.