delete query not working

try this

delete tblForParsing.*
from tblForParsing
where tblForParsing.[task/activity#] in(select [task/activity#] from [delete these activities])


will delete everything where  tblForParsing.[task/activity#] = [delete these activities].[task/activity#]



or this one,

delete tblForParsing.*
from tblForParsing
where tblForParsing.[task/activity#] not in(select [task/activity#] from [delete these activities])

will delete everything where  tblForParsing.[task/activity#] <> [delete these activities].[task/activity#]

See here as well:
http://support.microsoft.com/kb/240098

Also note that if the Delete query includes an Non-update-able query, then this could also cause the error

thanks so much for your help capricon1. i tried it but it still doesn't work. here are the screenshots
5.png
6.png

thanks boag2000, the Unique Records was set to no just now, i just set it to Yes but it still can't work

developingprogrammer,

change the name of the fields accordingly..

[task/activity#]  with [task/activity #]

delete tblForParsing.*
from tblForParsing
where tblForParsing.[task/activity #] in(select [task/activity #] from [delete these activities])

whao cool it works!! so sorry capricon1 for not spotting such an elementary fix.

i've got a short wrap up question. how you did it was by using written out sql. can you show me how i can do it using the access QBE? how come my previous method / screenshots couldn't work?

i'm trying to learn how to do everything via QBE so that i outsource the SQL to access thus minimising my mistakes at this stage

<how come my previous method / screenshots couldn't work?>

because you are using a join in your query.

ok i believe you're referring to my base query which identifies which activities to delete cause for my delete query i've tried both with and without join.

for my base query this is how i'm doing it. how should i do it via QBE without using an outer join?
8.png

ok re-reading the thread a bit i realised it can't be the base query you were talking about because in your example which works, you're refering to my base query in your SQL - which verifies that my base query is ok.

so it must be the delete query. so if the joins were causing the problem, i removed the join already but i'm not sure how come the query still can't work = ((

could someone help me out? i'm eager to find out what i was doing wrong. thanks guys!!
9.png

comparison of query that work and query that doesn't work - how do i make the query work using purely QBE?

hi DoDahD, thanks so much for your great suggestion! i looked at the SQL and this is what i've got. could you kindly share with me how i can get the function of the working query through using QBE? i'm not sure how to do it now though i've tried quite a few ways already.

this query doesn't work

DELETE tblDataStorage.*, tblDataStorage.[Task/Activity #]
FROM [DELETE THESE ACTIVITIES], tblDataStorage
WHERE (((tblDataStorage.[Task/Activity #]) In ([DELETE THESE ACTIVITIES].[Task/Activity #])));

this query works

DELETE tblForParsing.*, tblForParsing.[task/activity #]
FROM tblForParsing
WHERE (((tblForParsing.[task/activity #]) In (select [task/activity #] from [delete these activities])));

In this delete query:
you have no relationship established.  In QBE, the relationship is the line from one table to the next in the top half of the window.

This should be able to be fixed by dragging the [Task/Activity #] field from one table and dropping it on the matching field in the other table.  This creates a linked relationship between the tables where the records in [DELETE THESE ACTIVITIES] will be used to match records in [tblDataStorage] to determine when to delete records.

As always, when testing, it is safest to backup first and/or to test in a copy of the data!!!!!!!

Capricorn,

I suspect that you are correct in your suggestion.   When you establish the link in the QBE window, Access will correct the SQL to reflect the relationship.

fantastic guys it works!! thanks so much guys!!!!