473,839 Members | 1,594 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Access 2k/2003: Touble with delete query using WHERE EXISTS

I have a table that I want to delete specific records from based on
data in other tables. I'm more familiar with Access '97, but am now
using 2003, but the database is in 2000 format. In '97, I think I
could have easily done this using joins, but I kept getting "could not
delete from specified tables" errors. Some google searching has
indicated I need to use a subquery. After many failed attempts with
different approaches, I finally did a couple of upstream queries to
give me the exast list of records I want to delete from the table, and
the output of that query only contains the four key fields of the table
I want to delete records from. Following is my query:

DELETE FROM FilterJobGroupT ally
WHERE EXISTS
(SELECT *
FROM qryFilterDelete LastPre
WHERE [FilterJobGroupT ally].[JobID] = [qryFilterDelete LastPre].[JobID]
AND [FilterJobGroupT ally].[FilterJobGroupI D] =
[qryFilterDelete LastPre].[FilterJobGroupI D] AND
[FilterJobGroupT ally].[FilterTypeID] =
[qryFilterDelete LastPre].[FilterTypeID] AND
[FilterJobGroupT ally].[FilterSizeID] =
[qryFilterDelete LastPre].[FilterSizeID]);

When I run this, it tries to delete ALL the records in
FilterJobGroupT ally, not just the ones that exist in the subquery
qryFilterDelete LastPre. What am I doing wrong?

Thanks!

Nov 13 '05 #1
14 8094
Try the Microsoft Knowledge Base article:

http://support.microsoft.com/?id=207761

--
HTH
Van T. Dinh
MVP (Access)
"Darin" <go****@darincl ine.com> wrote in message
news:11******** *************@z 14g2000cwz.goog legroups.com...
I have a table that I want to delete specific records from based on
data in other tables. I'm more familiar with Access '97, but am now
using 2003, but the database is in 2000 format. In '97, I think I
could have easily done this using joins, but I kept getting "could not
delete from specified tables" errors. Some google searching has
indicated I need to use a subquery. After many failed attempts with
different approaches, I finally did a couple of upstream queries to
give me the exast list of records I want to delete from the table, and
the output of that query only contains the four key fields of the table
I want to delete records from. Following is my query:

DELETE FROM FilterJobGroupT ally
WHERE EXISTS
(SELECT *
FROM qryFilterDelete LastPre
WHERE [FilterJobGroupT ally].[JobID] = [qryFilterDelete LastPre].[JobID]
AND [FilterJobGroupT ally].[FilterJobGroupI D] =
[qryFilterDelete LastPre].[FilterJobGroupI D] AND
[FilterJobGroupT ally].[FilterTypeID] =
[qryFilterDelete LastPre].[FilterTypeID] AND
[FilterJobGroupT ally].[FilterSizeID] =
[qryFilterDelete LastPre].[FilterSizeID]);

When I run this, it tries to delete ALL the records in
FilterJobGroupT ally, not just the ones that exist in the subquery
qryFilterDelete LastPre. What am I doing wrong?

Thanks!

Nov 13 '05 #2
Thanks... I did see that, and have tried using DISTINCT in various
queries of various forms to get this to happen, and still no luck. It
didn't help the previous queries where it said "could not delete from
specified tables", and it doesn't help this latest version that I
posted here. With or without, the results are the same. It wants to
delete every record in the table. I'm at least optimistic now that I'm
to a point where it CAN delete records, now I just have to get it so it
only deletes the ones I want it to! Yesterday, when it absolutely no
way would even try to delete data, I was on the verge of going the
"poor database design" route, and just adding a yes/no field to the
table, with an update query to make those records I want to delete as
"yes", and a subsequent SIMPLE delete query to delete the ones that are
yes. I really don't want to have to go that route though. ;-)

Nov 13 '05 #3
you know, i never used Exists.

Why not just use the in caluse?

DELETE FROM FilterJobGroupT ally
WHERE JobID in (12, 13, 14)

You can replace the "in" part with a select query as follows:

DELETE FROM FilterJobGroupT ally WHERE JobID in
(SELECT JodID FROM qryFilterDelete LastPre
WHERE bla bla bla)

So, get that "in" query working seperately that retun the reocrds you want.

I notice that:
[FilterJobGroupT ally].[JobID] = [qryFilterDelete LastPre].[JobID]

You got two different tables in that sub-query, but I don't see the join, or
table placed in for the 2nd table. You need to specify that 2nd table in the
join, or a implied join in a where clause. As I mentioned, get the query
working alone, and then put that query in the above "in" clause....

The above approach is a good alternative to exists....

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl************* ****@msn.com
http://www.members.shaw.ca/AlbertKallal
Nov 13 '05 #4
Post relevant Table Structure indicating PK and als the SQL of the Query
"qryFilterDelet eLastPre".

--
HTH
Van T. Dinh
MVP (Access)
"Darin" <go****@darincl ine.com> wrote in message
news:11******** **************@ g49g2000cwa.goo glegroups.com.. .
Thanks... I did see that, and have tried using DISTINCT in various
queries of various forms to get this to happen, and still no luck. It
didn't help the previous queries where it said "could not delete from
specified tables", and it doesn't help this latest version that I
posted here. With or without, the results are the same. It wants to
delete every record in the table. I'm at least optimistic now that I'm
to a point where it CAN delete records, now I just have to get it so it
only deletes the ones I want it to! Yesterday, when it absolutely no
way would even try to delete data, I was on the verge of going the
"poor database design" route, and just adding a yes/no field to the
table, with an update query to make those records I want to delete as
"yes", and a subsequent SIMPLE delete query to delete the ones that are
yes. I really don't want to have to go that route though. ;-)

Nov 13 '05 #5
"Post relevant Table Structure indicating PK and als the SQL of the
Query
"qryFilterDelet eLastPre". "

Wow, ok, I'll try to do this as succinctly as I can...

The table that has the records I want to delete, FilterJobGroupT ally,
has four key fields, as shown in my original message. There are other
fields, which I don't think are relevent to the discussion. It is
linked to some other tables, but always on the "many" side.
Deleting/editing records from FilterJobGroupT ally has no affect on
other tables. I have worked up to a point where I now have three
queries upstream of this query. I don't think I need that many, it's
just the many iterations I've tried to get something to work.
Regardless, here is the structure of qryFilterDelete LastPre:

SELECT DISTINCTROW FilterJobGroupT ally.JobID,
FilterJobGroupT ally.FilterJobG roupID, FilterJobGroupT ally.FilterSize ID,
FilterJobGroupT ally.FilterType ID
FROM FilterJobGroupT ally LEFT JOIN qryFilterDelete Pre ON
(FilterJobGroup Tally.FilterTyp eID = qryFilterDelete Pre.FilterTypeI D)
AND (FilterJobGroup Tally.FilterSiz eID =
qryFilterDelete Pre.FilterSizeI D) AND
(FilterJobGroup Tally.FilterJob GroupID =
qryFilterDelete Pre.FilterJobGr oupID) AND (FilterJobGroup Tally.JobID =
qryFilterDelete Pre.JobID)
WHERE
(((FilterJobGro upTally.JobID)=[Forms]![JobFilters]![FilterJobGroups _subform].[Form]![JobID])
AND
((FilterJobGrou pTally.FilterJo bGroupID)=[Forms]![JobFilters]![FilterJobGroups _subform].[Form]![GroupID])
AND ((qryFilterDele tePre.JobID) Is Null))

The purpose of this query was basically to just invert the selection...
the query upstream of this (qryFilterDelet ePre) returns the records
that should be kept. I had tried using that query directly with my
final query, using NOT EXISTS, but it always resulted in no records
beind deleted (it didn't fail, just said 0 records were going to be
deleted). And in case you're interested, here is the SQL of
qryFilterDelete Pre:

SELECT DISTINCT FilterJobGroupT ally1.JobID,
FilterJobGroupT ally1.FilterJob GroupID,
FilterJobGroupT ally1.FilterSiz eID, FilterJobGroupT ally1.FilterTyp eID
FROM FilterJobGroupT ally AS FilterJobGroupT ally1 INNER JOIN
qryFilterDelete PrePre ON (FilterJobGroup Tally1.FilterSi zeID =
qryFilterDelete PrePre.FilterSi zeID) AND (FilterJobGroup Tally1.JobID =
qryFilterDelete PrePre.JobID) AND (FilterJobGroup Tally1.FilterJo bGroupID
= qryFilterDelete PrePre.GroupID)
WHERE
(((FilterJobGro upTally1.JobID) =[Forms]![JobFilters]![FilterJobGroups _subform].[Form]![JobID])
AND
((FilterJobGrou pTally1.FilterJ obGroupID)=[Forms]![JobFilters]![FilterJobGroups _subform].[Form]![GroupID])
AND
((FilterJobGrou pTally1.FilterT ypeID)=[qryFilterDelete PrePre].[FilterTypeID])
AND ((qryFilterDele tePrePre.PreFil ter)=False)) OR
(((FilterJobGro upTally1.JobID) =[Forms]![JobFilters]![FilterJobGroups _subform].[Form]![JobID])
AND
((FilterJobGrou pTally1.FilterJ obGroupID)=[Forms]![JobFilters]![FilterJobGroups _subform].[Form]![GroupID])
AND
((FilterJobGrou pTally1.FilterT ypeID)=[qryFilterDelete PrePre].[PreFilterTypeID])
AND ((qryFilterDele tePrePre.PreFil ter)=True));

This query also has another query upstream of it that may not be
necessary... I originally had the upstream query integrated into this
one, but tried breakign it out into another query only because Access
help insinuates that there may be times when you can't do a delete
query when there are more than three tables involved.

At this point, it's probably easier just to post my entire database.
;-)

Nov 13 '05 #6
So, get that "in" query working seperately that retun the reocrds you
want.
I notice that:
"[FilterJobGroupT ally].[JobID] = [qryFilterDelete LastPre].[JobI*D]
You got two different tables in that sub-query, but I don't see the
join, or
table placed in for the 2nd table. You need to specify that 2nd table
in the
join, or a implied join in a where clause. As I mentioned, get the
query
working alone, and then put that query in the above "in" clause.... "

As I mentioned in another reply, I thought I'd have problems with
multiple keys if I tried to use IN (??). Also, my existing subquery
already works on it's own... it correctly returns only the records I
want to delete. But when put into my final query, the results are ALL
records are returned. As far as joins, that is how I started, as that
is what I'm used to doing in '97. But EVERY time I tried to use joins,
I got the "could not delete from specified tables" error. Google
searching found posts with people in situations like mine, and the
responses were alsways that subqueries need to be used instead of
exists on delete queries, which is what brought me where I am now.

Nov 13 '05 #7
Well, if you query returns the one column of ids to delete..then does note
using the in idea work?

"in" should work if you supply a list, or a query for the source....
So, modify that query to return just the id's you want to delete, and then
put that query into the "in" clause...

(it is not clear if you are asking will this work, or if you tried it
already...and it don't....).
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl************* ****@msn.com
http://www.members.sha w.ca/AlbertKallal'
Nov 13 '05 #8
"...(it is not clear if you are asking will this work, or if you tried
it
already...and it don't....). "

No, I hadn't tried it, because I didn't think it would work for the
reasons I posted above (deleting more fields than it should, because
the four key fields are looked at individually, as opposed to combined
as one unique combination). But in the interest of trying anything,
I've tried it now. But as I feared, it returned more records than it
should have (but at least it didn't try to delete ALL the records).
Following is my SQL... perhaps I'm just not using it correctly:

DELETE *
FROM FilterJobGroupT ally
WHERE FilterJobGroupT ally.JobID IN
(SELECT [FilterJobGroupT ally].[JobID] AS Expr1
FROM qryFilterDelete LastPre
WHERE
((([FilterJobGroupT ally].[JobID])=[qryFilterDelete LastPre].[JobID]) AND
(([FilterJobGroupT ally].[FilterJobGroupI D])=[qryFilterDelete LastPre].[FilterJobGroupI D])
AND
(([FilterJobGroupT ally].[FilterTypeID])=[qryFilterDelete LastPre].[FilterTypeID])
AND
(([FilterJobGroupT ally].[FilterSizeID])=[qryFilterDelete LastPre].[FilterSizeID])))
AND
FilterJobGroupT ally.FilterJobG roupID IN
(SELECT [FilterJobGroupT ally].[FilterJobGroupI D] AS Expr1
FROM qryFilterDelete LastPre
WHERE
((([FilterJobGroupT ally].[JobID])=[qryFilterDelete LastPre].[JobID]) AND
(([FilterJobGroupT ally].[FilterJobGroupI D])=[qryFilterDelete LastPre].[FilterJobGroupI D])
AND
(([FilterJobGroupT ally].[FilterTypeID])=[qryFilterDelete LastPre].[FilterTypeID])
AND
(([FilterJobGroupT ally].[FilterSizeID])=[qryFilterDelete LastPre].[FilterSizeID])))
AND
FilterJobGroupT ally.FilterType ID IN
(SELECT [FilterJobGroupT ally].[FilterTypeID] AS Expr1
FROM qryFilterDelete LastPre
WHERE
((([FilterJobGroupT ally].[JobID])=[qryFilterDelete LastPre].[JobID]) AND
(([FilterJobGroupT ally].[FilterJobGroupI D])=[qryFilterDelete LastPre].[FilterJobGroupI D])
AND
(([FilterJobGroupT ally].[FilterTypeID])=[qryFilterDelete LastPre].[FilterTypeID])
AND
(([FilterJobGroupT ally].[FilterSizeID])=[qryFilterDelete LastPre].[FilterSizeID])))
AND
FilterJobGroupT ally.FilterSize ID IN
(SELECT [FilterJobGroupT ally].[FilterSizeID] AS Expr1
FROM qryFilterDelete LastPre
WHERE
((([FilterJobGroupT ally].[JobID])=[qryFilterDelete LastPre].[JobID]) AND
(([FilterJobGroupT ally].[FilterJobGroupI D])=[qryFilterDelete LastPre].[FilterJobGroupI D])
AND
(([FilterJobGroupT ally].[FilterTypeID])=[qryFilterDelete LastPre].[FilterTypeID])
AND
(([FilterJobGroupT ally].[FilterSizeID])=[qryFilterDelete LastPre].[FilterSizeID])))

Nov 13 '05 #9
OMG, I finally fixed it. I'll post back details later. Not something
I expected.

Nov 13 '05 #10

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

Similar topics

1
8913
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 automatically deletes also all those rows in the child table whose foreign key values are equal to the referenced key value in the parent row. However:
5
5763
by: Zalek Bloom | last post by:
Hello, I am developing a simple ASP application with VBScript and Access database. I am testing it on my Win98SE machine using Personal Web Server. My machine is Celeron 2000 with 512Mb RAM. On the Access I have 2 tables, each one with less then 20 rows. The problem is, that each time my application access the database, the response is about 30 second. My questions: 1. How can I improve response time?
23
2703
by: JustMe | last post by:
I don't know if this has anything to do with AccessXP running on Terminal Services with Access97/2000 also installed, but here is one example of a query that does not work any longer: SELECT DLookUp("MajorCategory","AutoTypes","='1'") AS MajorCategory, PreOwned.PaintColor FROM PreOwned; When I try to run the above query I get the following error:
0
4204
by: Mike Knight | last post by:
(I've also posted this problem on microsoft.public.excel.programming) I have a MS Access 2003 Database named "AS400 Fields.mdb". This database contains links to tables on an AS400. In MS Excel 2003, I have VBA code that creates and executes queries using the Access database, and returns the results to an Excel sheet. The first time the query is executed, results are returned to Excel in usually less than 10 seconds. However, if the...
6
4190
by: Tim Marshall | last post by:
A2003, but this behaviour also occurred during the same DAO process I'm about to describe in A97. I have a sub procedure which takes two arguments: a querydef name; and an SQL statement constructed from the calling procedure and constructs or modifies a querydef. The sub runs through the querydefs collection - if it finds the querydef name argument, it takes the existing query and modifies the SQL. If not found, a new querydef is...
52
10018
by: Neil | last post by:
We are running an Access 2000 MDB with a SQL 7 back end. Our network guy is upgrading to Windows Server 2003 and wants to upgrade Office and SQL Server at the same time. We're moving to SQL Server 2005, and, since he already has licenses for Office Pro 2002, he wants to upgrade to that. I've been saying that we need to upgrade to Access 2003, not 2002, even if Office is kept at 2002. We are also looking to do a fair amount of...
2
2026
by: Robin | last post by:
I have a main table that I need to delete records that arn't referenced in another. Query says I cannot delete. If I remove the reference query all deletes ok. Hope there is a way around this ? Query as follows: DELETE Items.*, Items.SupplierCode, Items.UpdatedDateTime, .ItemNumber FROM RIGHT JOIN Items ON .ItemNumber = Items.ItemNumber WHERE (((Items.SupplierCode)=!!) AND
3
7777
by: mnjkahn via AccessMonster.com | last post by:
I'm running Access 2003, modifying a query that has over 45 fields. When I right click on the field name in Query Design View, and then click Build, Access crashes before the Build window appears. It doesn't happen every time, and using the Zoom window works fine. It appears that it only happens when I want to modify an existing expression. This continues to happen even after the database is repaired and reopened. Anyone have any...
1
2462
by: Bobby | last post by:
Hi I am using Access 2003 mdb as a front end to an application which uses SQL Server 2000 as the backend. The two are connected using ODBC. On one particular table (the Stock table), I have a simple form which updates stock. When the user presses the delete key, he gets the usual warning about not being able to undo this command, and the record disappears from the screen. However, on some records, when he goes back into the form, the...
0
9856
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
10910
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10589
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10297
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9426
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7833
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 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 a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5867
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4493
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
3136
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.