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

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

http://www.members.shaw.ca/AlbertKallal'

"...(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])))

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

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! :-)

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

http://www.members.shaw.ca/AlbertKallal

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

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

http://www.members.shaw.ca/AlbertKallal

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