Difficulty in Creating a Delete Query

[Petrol]

I have three related tables, ATI, WPH and Selected Event.
API and WPH are related through the common field PersonID,
while WPH and SelectedEvent are related through the common field WalkNumber.
I want to create an Access SQL DELETE query to delete all records in WPH
where WPH.WalkNumber = SelectedEvent.WalkNumber
and ATI.Response IN ("C","D");
but I can't quite get an SQL statement that works.

I tried creating the query through the design view, and the resultant SQL was

DELETE Walk_participation_history.*, Applicant_Transient_info.Response
FROM (Walk_participation_history INNER JOIN SelectedEvent ON Walk_participation_history.WalkNumber = SelectedEvent.WalkNum) INNER JOIN Applicant_Transient_info ON Walk_participation_history.PersonID = Applicant_Transient_info.PersonID
WHERE (((Applicant_Transient_info.Response) In ("C","D")));

But when this is run it generates the error "Specify the table containing the records you want to delete".
I thought a Select query was equivalent to a table, but evidently not.

Is what I want to achieve doable?

[NeoPa]

Petrol:
I thought a Select query was equivalent to a table, but evidently not.

In some circumstances it can be, but whatever happens you need to ensure that the part of the SQL that specifies the dataset (Table or SELECT Query) is updatable (Reasons for a Query to be Non-Updatable).

Sometimes that means taking out that part of the query and using it as a SELECT query. Access will allow you to make changes to the resulting data if, but only if, it's updatable.

[Petrol]

That could be the problem, but the Microsoft article you refer to isn't very clear about 3-table queries. The specific example they quote (many-one-many) doesn't apply here, but maybe my configurtion is not updateable either.
I have a fallback strategy (use MakeTable to put the relevant records in a temp table and then try to delete the WPH from there) but I was hoping I could find a more elegant solution that works.

However, I am intrigued by Microsoft's statement that "It is provided as is, for anyone who may still be using these technologies". That seems to imply that there ae newer and better technologies, n'est-ce pas? Newer and better than update queries? Newer and better than MS Access??

[NeoPa]

I suspect that's simply a generic disclaimer for items that are for older versions. The date is in 2014 so probably is version-specific to Access (Office) 2013. It isn't a comment referring to Access as a whole & as this is probably ACE-related I doubt it's changed since then or that it might any time in a foreseeable future.

Be careful reading too much into very generic comments - especially when found in old links. This one was dug up from my list of sites I've put away back in the day for reference & I've been doing this for a while now ;-)

[NeoPa]

Petrol:
That could be the problem, but the Microsoft article you refer to isn't very clear about 3-table queries.

Indeed not. It describes principles rather than every possible situation. The info is there; if you need it for more complicated situations then you'll need to work at applying the logic. Of course, a quick test is never that hard and you always have that to help give the answer - even if understanding the why of it may be complicated.

[isladogs]

If the delete query isn't updateable, which seems very likely for a 3 table query, try changing it to specify unique records using
DELETE DISTINCTROW ....

[Petrol]

OK, thanks, everyone. In the end I did manage to find some SQL code that worked.

DELETE Walk_participation_History.PersonID, *
FROM Walk_participation_History
WHERE (((Walk_participation_History.PersonID) In (SELECT Walk_participation_history.PersonID
FROM ( ( (Walk_participation_history 
INNER JOIN 
Applicant_Transient_info ON Walk_participation_history.PersonID = Applicant_Transient_info.PersonID)
 INNER JOIN 
Walks ON Walk_participation_history.WalkNumber = Walks.WalkNumber) 
INNER JOIN SelectedEvent ON SelectedEvent.WalkNum = Walks.WalkNumber)
 
WHERE ((Applicant_Transient_info.Response)="C" Or (Applicant_Transient_info.Response)="D") AND ((Walk_participation_history.Posn_ID)=0)   )));
 

My apologies for troubling you, but it did lead to some useful learning on my part, so thanks again.
(As is probably obvious, ATI and WPH in the OP were abbreviated versions of the real table names).

[NeoPa]

Petrol:
My apologies for troubling you, but it did lead to some useful learning on my part, so thanks again.

Not at all. It's why we're here & very little is more the point than that you're learning and getting a better understanding.
Well done :-)