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

[Darin]

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 FilterJobGroupTally
WHERE EXISTS
(SELECT *
FROM qryFilterDeleteLastPre
WHERE [FilterJobGroupTally].[JobID] = [qryFilterDeleteLastPre].[JobID]
AND [FilterJobGroupTally].[FilterJobGroupID] =
[qryFilterDeleteLastPre].[FilterJobGroupID] AND
[FilterJobGroupTally].[FilterTypeID] =
[qryFilterDeleteLastPre].[FilterTypeID] AND
[FilterJobGroupTally].[FilterSizeID] =
[qryFilterDeleteLastPre].[FilterSizeID]);

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

Thanks!

[Van T. Dinh]

Try the Microsoft Knowledge Base article:

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

--
HTH
Van T. Dinh
MVP (Access)
"Darin" <go****@darincline.com> wrote in message
news:11*********************@z14g2000cwz.googlegro ups.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 FilterJobGroupTally
WHERE EXISTS
(SELECT *
FROM qryFilterDeleteLastPre
WHERE [FilterJobGroupTally].[JobID] = [qryFilterDeleteLastPre].[JobID]
AND [FilterJobGroupTally].[FilterJobGroupID] =
[qryFilterDeleteLastPre].[FilterJobGroupID] AND
[FilterJobGroupTally].[FilterTypeID] =
[qryFilterDeleteLastPre].[FilterTypeID] AND
[FilterJobGroupTally].[FilterSizeID] =
[qryFilterDeleteLastPre].[FilterSizeID]);

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

Thanks!

[Darin]

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. ;-)

[Albert D. Kallal]

you know, i never used Exists.

Why not just use the in caluse?

DELETE FROM FilterJobGroupTally
WHERE JobID in (12, 13, 14)

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

DELETE FROM FilterJobGroupTally WHERE JobID in
(SELECT JodID FROM qryFilterDeleteLastPre
WHERE bla bla bla)

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

I notice that:
[FilterJobGroupTally].[JobID] = [qryFilterDeleteLastPre].[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

[Van T. Dinh]

Post relevant Table Structure indicating PK and als the SQL of the Query
"qryFilterDeleteLastPre".

--
HTH
Van T. Dinh
MVP (Access)
"Darin" <go****@darincline.com> wrote in message
news:11**********************@g49g2000cwa.googlegr oups.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. ;-)

[Darin]

"Post relevant Table Structure indicating PK and als the SQL of the
Query
"qryFilterDeleteLastPre". "

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

The table that has the records I want to delete, FilterJobGroupTally,
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 FilterJobGroupTally 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 qryFilterDeleteLastPre:

SELECT DISTINCTROW FilterJobGroupTally.JobID,
FilterJobGroupTally.FilterJobGroupID, FilterJobGroupTally.FilterSizeID,
FilterJobGroupTally.FilterTypeID
FROM FilterJobGroupTally LEFT JOIN qryFilterDeletePre ON
(FilterJobGroupTally.FilterTypeID = qryFilterDeletePre.FilterTypeID)
AND (FilterJobGroupTally.FilterSizeID =
qryFilterDeletePre.FilterSizeID) AND
(FilterJobGroupTally.FilterJobGroupID =
qryFilterDeletePre.FilterJobGroupID) AND (FilterJobGroupTally.JobID =
qryFilterDeletePre.JobID)
WHERE
(((FilterJobGroupTally.JobID)=[Forms]![JobFilters]![FilterJobGroups_subform].[Form]![JobID])
AND
((FilterJobGroupTally.FilterJobGroupID)=[Forms]![JobFilters]![FilterJobGroups_subform].[Form]![GroupID])
AND ((qryFilterDeletePre.JobID) Is Null))

The purpose of this query was basically to just invert the selection...
the query upstream of this (qryFilterDeletePre) 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
qryFilterDeletePre:

SELECT DISTINCT FilterJobGroupTally1.JobID,
FilterJobGroupTally1.FilterJobGroupID,
FilterJobGroupTally1.FilterSizeID, FilterJobGroupTally1.FilterTypeID
FROM FilterJobGroupTally AS FilterJobGroupTally1 INNER JOIN
qryFilterDeletePrePre ON (FilterJobGroupTally1.FilterSizeID =
qryFilterDeletePrePre.FilterSizeID) AND (FilterJobGroupTally1.JobID =
qryFilterDeletePrePre.JobID) AND (FilterJobGroupTally1.FilterJobGroupID
= qryFilterDeletePrePre.GroupID)
WHERE
(((FilterJobGroupTally1.JobID)=[Forms]![JobFilters]![FilterJobGroups_subform].[Form]![JobID])
AND
((FilterJobGroupTally1.FilterJobGroupID)=[Forms]![JobFilters]![FilterJobGroups_subform].[Form]![GroupID])
AND
((FilterJobGroupTally1.FilterTypeID)=[qryFilterDeletePrePre].[FilterTypeID])
AND ((qryFilterDeletePrePre.PreFilter)=False)) OR
(((FilterJobGroupTally1.JobID)=[Forms]![JobFilters]![FilterJobGroups_subform].[Form]![JobID])
AND
((FilterJobGroupTally1.FilterJobGroupID)=[Forms]![JobFilters]![FilterJobGroups_subform].[Form]![GroupID])
AND
((FilterJobGroupTally1.FilterTypeID)=[qryFilterDeletePrePre].[PreFilterTypeID])
AND ((qryFilterDeletePrePre.PreFilter)=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.
;-)

[Darin]

So, get that "in" query working seperately that retun the reocrds you
want.
I notice that:
"[FilterJobGroupTally].[JobID] = [qryFilterDeleteLastPre].[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.

[Albert D.Kallal]

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.shaw.ca/AlbertKallal'

[Darin]

"...(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 FilterJobGroupTally
WHERE FilterJobGroupTally.JobID IN
(SELECT [FilterJobGroupTally].[JobID] AS Expr1
FROM qryFilterDeleteLastPre
WHERE
((([FilterJobGroupTally].[JobID])=[qryFilterDeleteLastPre].[JobID]) AND
(([FilterJobGroupTally].[FilterJobGroupID])=[qryFilterDeleteLastPre].[FilterJobGroupID])
AND
(([FilterJobGroupTally].[FilterTypeID])=[qryFilterDeleteLastPre].[FilterTypeID])
AND
(([FilterJobGroupTally].[FilterSizeID])=[qryFilterDeleteLastPre].[FilterSizeID])))
AND
FilterJobGroupTally.FilterJobGroupID IN
(SELECT [FilterJobGroupTally].[FilterJobGroupID] AS Expr1
FROM qryFilterDeleteLastPre
WHERE
((([FilterJobGroupTally].[JobID])=[qryFilterDeleteLastPre].[JobID]) AND
(([FilterJobGroupTally].[FilterJobGroupID])=[qryFilterDeleteLastPre].[FilterJobGroupID])
AND
(([FilterJobGroupTally].[FilterTypeID])=[qryFilterDeleteLastPre].[FilterTypeID])
AND
(([FilterJobGroupTally].[FilterSizeID])=[qryFilterDeleteLastPre].[FilterSizeID])))
AND
FilterJobGroupTally.FilterTypeID IN
(SELECT [FilterJobGroupTally].[FilterTypeID] AS Expr1
FROM qryFilterDeleteLastPre
WHERE
((([FilterJobGroupTally].[JobID])=[qryFilterDeleteLastPre].[JobID]) AND
(([FilterJobGroupTally].[FilterJobGroupID])=[qryFilterDeleteLastPre].[FilterJobGroupID])
AND
(([FilterJobGroupTally].[FilterTypeID])=[qryFilterDeleteLastPre].[FilterTypeID])
AND
(([FilterJobGroupTally].[FilterSizeID])=[qryFilterDeleteLastPre].[FilterSizeID])))
AND
FilterJobGroupTally.FilterSizeID IN
(SELECT [FilterJobGroupTally].[FilterSizeID] AS Expr1
FROM qryFilterDeleteLastPre
WHERE
((([FilterJobGroupTally].[JobID])=[qryFilterDeleteLastPre].[JobID]) AND
(([FilterJobGroupTally].[FilterJobGroupID])=[qryFilterDeleteLastPre].[FilterJobGroupID])
AND
(([FilterJobGroupTally].[FilterTypeID])=[qryFilterDeleteLastPre].[FilterTypeID])
AND
(([FilterJobGroupTally].[FilterSizeID])=[qryFilterDeleteLastPre].[FilterSizeID])))

[Darin]

OMG, I finally fixed it. I'll post back details later. Not something
I expected.

[Darin]

What I ended up doing was working on getting the set-up back to where I
thought it should be (only ONE pre-query instead of a chain of queries
that feeds the sub-query). This meant switching back to feeding the
main query the records that should NOT be deleted, rather than the ones
that SHOULD, and therefore necessitated a switch to using WHERE NOT
EXISTS. So I tried to run it again after doing all that, and while it
still wasn't right, this time instead of trying to delete ALL the
records, I noticed that the number it wanted to delete was 5 less than
the total record count of the table. 5 just happens to be the number
of records that should have not been deleted in the current job/group I
was working on. I then realized that it was totally ignoring the
criteria in one of the upstream filters that narrows down the list to
only the records that belong to the jobID & groupID that the user is
currently working on (based on a reference to those two fields in the
user's current form, which you can see referenced in one of my
pre-queries detailed above). Strangely, that criteria worked through
all of the upstream queries, right down to the select statement in the
sub-query of the main query. But as soon as it was run from the main
query, it ignored the criteria that came from the form. The simple
solution (though I don't really understand why) is including that
criteria directly in the main query, rather than in the subquery. The
working SQL is:

DELETE *
FROM FilterJobGroupTally
WHERE (NOT EXISTS
(SELECT *
FROM qryFilterDeletePre
WHERE [FilterJobGroupTally].[JobID] = [qryFilterDeletePre].[JobID] AND
[FilterJobGroupTally].[FilterJobGroupID] =
[qryFilterDeletePre].[FilterJobGroupID] AND
[FilterJobGroupTally].[FilterTypeID] =
[qryFilterDeletePre].[FilterTypeID] AND
[FilterJobGroupTally].[FilterSizeID] =
[qryFilterDeletePre].[FilterSizeID]))
AND
((FilterJobGroupTally.JobID)=[Forms]![JobFilters]![FilterJobGroups_subform].[Form]![JobID])
AND
((FilterJobGroupTally.FilterJobGroupID)=[Forms]![JobFilters]![FilterJobGroups_subform].[Form]![GroupID]);
Thanks to all who offered suggestions! :-)

[Albert D.Kallal]

> Following is my SQL... perhaps I'm just not using it correctly:

Not sure, but does you query that returns the correct rocrds use a bunhc of
in clauses? (you did't add those in clases to that query to try and make
this work..did you ?).

My assuming here is that you got a query that you made in the query buidler
that returns all of the id's you want to delete.

So, build that query, and get it working.

You THEN go:

DELETE *
FROM FilterJobGroupTally
WHERE JobID IN (SELECT JobID from qryFilterDeleteLastPre)
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
http://www.members.shaw.ca/AlbertKallal

[Darin]

No, I never tried the IN clauses, in the final query or the
pre-queries, until the attempt I listed above after your suggestion. I
didn't try them because I didn't think they would work on multiple
fields, and that turned out to be true (and further reading on the
topic since has confirmed this). IN clauses ONLY work if you are
trying to select based on only one field. Your example above would
work, as it's only concerned about JobID. But as I mentioned before,
my table has four key fields. I ONLY want to delete specific records,
that are specific combinations of those four key fields that make those
records unique. I could use four IN clauses, like my example I tried
earlier, but each case would be independant of the other, and therefore
more records would get deleted than I intended (which would be bad).
IN is not capable of analyzing multiple fields at one time as unique
combinations. I don't want to delete all the records that match a
JobID or a GroupID or a TypeID or a SizeID. That would be the
equivalent of using joins in a query with multiple key tables, but not
joining all the keys. You would get back more results than you want.
The four key fields combine into one unique key, and I need to delete
very specific records that are determined by the values of those four
keys.

Anyway, as I posted a little earlier, I finally got it fixed. It seems
the EXISTS statement ignores other cirteria within the sub-query that
is not directly related to the fields joining the main & sub queries.
I've moved some criteria out of the pre-queries and into the main
query, and it now works as it should. All is good. :-)

[Albert D.Kallal]

>Your example above would
work, as it's only concerned about JobID. But as I mentioned before,
my table has four key fields.

Ah, excellent, ok. My solution in these cases is to ALWAYS add a autonumber
field to a table - doing this would make this a trivial problem. I kind of
assumed that was the case here. With a autonumber pk, then you simply then
select the autonumber id based on your conditions...and away you go.....

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
http://www.members.shaw.ca/AlbertKallal

[Darin]

Yes, adding an autonumber field to every table could certainly make
things easier from the perspective of only having to deal with one key,
but with the type of data I deal with, and the amount of
inter-relations they have, multiple keys seems much more logical. In
this particular case, the criteria that determined which records to
delete was the result of data in multiple tables, so I still would have
had to look at the various combinations of four fields. If an
autonumber were the key field instead, then I could have determined
what those numbers were in an upstream query, but now that I know how
to deal with multiple keys using EXISTS, the end result is the same. I
hate that I spent so much time on one small issue, but I learned a lot
about EXISTS along the way. :-)