473,287 Members | 1,659 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

Difficulty in Creating a Delete Query

204 128KB
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
Expand|Select|Wrap|Line Numbers
  1. DELETE Walk_participation_history.*, Applicant_Transient_info.Response
  2. 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
  3. 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?
Jan 23 '23 #1

✓ answered by 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.

7 3650
NeoPa
32,554 Expert Mod 16PB
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.
Jan 23 '23 #2
Petrol
204 128KB
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??
Jan 24 '23 #3
NeoPa
32,554 Expert Mod 16PB
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 ;-)
Jan 24 '23 #4
NeoPa
32,554 Expert Mod 16PB
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.
Jan 24 '23 #5
isladogs
454 Expert Mod 256MB
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 ....
Jan 24 '23 #6
Petrol
204 128KB
OK, thanks, everyone. In the end I did manage to find some SQL code that worked.
Expand|Select|Wrap|Line Numbers
  1. DELETE Walk_participation_History.PersonID, *
  2. FROM Walk_participation_History
  3. WHERE (((Walk_participation_History.PersonID) In (SELECT Walk_participation_history.PersonID
  4. FROM ( ( (Walk_participation_history 
  5. INNER JOIN 
  6. Applicant_Transient_info ON Walk_participation_history.PersonID = Applicant_Transient_info.PersonID)
  7.  INNER JOIN 
  8. Walks ON Walk_participation_history.WalkNumber = Walks.WalkNumber) 
  9. INNER JOIN SelectedEvent ON SelectedEvent.WalkNum = Walks.WalkNumber)
  10.  
  11. WHERE ((Applicant_Transient_info.Response)="C" Or (Applicant_Transient_info.Response)="D") AND ((Walk_participation_history.Posn_ID)=0)   )));
  12.  
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).
Jan 25 '23 #7
NeoPa
32,554 Expert Mod 16PB
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 :-)
Jan 25 '23 #8

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: Craig Jurney | last post by:
Am having difficulty creating a dynamic <select> element using direct assignment to the element's option array (ie. someElement.option=new Option(someText, someValue);) that will work on Palm...
2
by: Andrea | last post by:
I'm having some difficulty creating a report in Access and I need some suggestions. My company issues "Return Authorizations" when customers need to return products. A customer calls in and we...
1
by: longtim | last post by:
I have been having endless difficulty creating reports/queries that set any relevent parameters from controls in forms. I am creating an application under access 2003 but will target access...
3
by: wheresjim | last post by:
I am trying this project out: http://www.codeproject.com/useritems/javacsharp.asp I am having difficulty building parts of it with Visual Studio .NET 2003 because of a post-build step that...
5
by: gazelle04 | last post by:
How can make a Delete query based on two tables. Lets say the primary table where I want to make deletion is "Customers" and then the related table is "CustomerOrders". I want to delete the data from...
0
by: jmarr02s | last post by:
I am using Access 2003 and am having difficulty creating a subform that will save records associated with a particular parent record.... The error message I receive pertains to indexes,...
15
by: samimmu | last post by:
actually i have a lot of problems with code down. i actually i made this code to let create a file for several student, i tried by my name but unfortunately i got alot of errors i don't know how...
5
by: Rico | last post by:
Hello, I'm trying to create a simple back up in the SQL Maintenance Plan that will make a single back up copy of all database every night at 10 pm. I'd like the previous nights file to be...
0
by: Douglas Zuniga | last post by:
First at al Hi. I'm having some problems trying to connect my C# program-classes to Java, I've been reading about it and I found this:...
7
cori25
by: cori25 | last post by:
I have 4 database's that I need to enter and run a macro in. The macro in each db is named the same. I am having difficulty creating a loop to go into one db run the macro exit and then go into...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

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.