UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Delete Query From Multiple Tables With Where Clause, Access 2013    
 
   
cjduncan
post Jun 25 2019, 01:01 PM
Post#1



Posts: 32
Joined: 30-April 19



Hi!

I want to delete the records in multiple tables where the control on a form is equal to a field in those records.

Specifically, delete the SOIID and all it's related records in the other tables that equal the SOIID on form 4_1_SOI. Since using the query wizard makes me choose a field, how do I do this? Because currently I'm getting an error that says to define which tables to delete from.


Attached File(s)
Attached File  qrydesign.PNG ( 21.17K )Number of downloads: 5
Attached File  form.PNG ( 42.18K )Number of downloads: 2
 
Go to the top of the page
 
theDBguy
post Jun 25 2019, 01:35 PM
Post#2


Access Wiki and Forums Moderator
Posts: 75,705
Joined: 19-June 07
From: SunnySandyEggo


Hi. I could be wrong but I think a delete query can only delete records from one table at a time. So, maybe the easiest/fastest way to delete records from multiple tables is to do them one at a time in sequence.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
GroverParkGeorge
post Jun 25 2019, 01:43 PM
Post#3


UA Admin
Posts: 35,301
Joined: 20-June 02
From: Newcastle, WA


I agree. It's almost never possible to delete from multiple tables simultaneously.

Start with the records in the "child" side tables first, then their parent records.

Allen Browne llisted some factors that make a query read only.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
pere_de_chipstic...
post Jun 25 2019, 02:05 PM
Post#4


UtterAccess Editor
Posts: 10,480
Joined: 8-November 07
From: South coast, England


Would a Cascade Delete relationship do you what you want?

--------------------
Warm regards
Bernie
Go to the top of the page
 
ADezii
post Jun 25 2019, 02:12 PM
Post#5



Posts: 2,457
Joined: 4-February 07
From: USA, Florida, Delray Beach


  1. You can DELETE Records in Multiple Tables with a single DELETE Query if your have RI (Referential Integrity) with Cascade Delete Related Records enforced. This is exactly the scenario in the Graphic posted below.
  2. Demo Query Definition:
    SQL
    Dim strSQL As String

    strSQL = "DELETE * FROM Suppliers WHERE Suppliers.[ID] = 8

    CurrentDB.Execute strSQL, dbFailOnError
  3. Deleting Supplier #8 in the Suppliers will Delete all Related Records in Purchase Orders where the [Supplier ID was 8]. Consequently, all Records in Purchase Order Details will be Deleted that have the same [Purchase Order ID] of those Records that were Deleted in Purchase Orders.
  4. This illustrates how you can Delete Records in three Tables with a single Delete Records Query as long as RI and Cascade Delete Related Records are strategically enforced.
  5. Be advised, that as handy a feature this may be, that's just as dangerous as it can be.
  6. Hope this helps.

P.S. - If properly structured, Deleting a Record(s) in the SOI Table can also Delete Record(s) in the other three Tables (SOIMI, SOILESS, SOIMI2OBJ).
This post has been edited by ADezii: Jun 25 2019, 02:26 PM
Attached File(s)
Attached File  Pic.JPG ( 31.79K )Number of downloads: 2
 
Go to the top of the page
 
GroverParkGeorge
post Jun 25 2019, 02:27 PM
Post#6


UA Admin
Posts: 35,301
Joined: 20-June 02
From: Newcastle, WA


Good points, thanks.

I was thinking of Cascade Delete when I said, "almost never possible". It's a two-edged sword, of course, because the deletions can remove child records silently, with no warning that's it's going to happen. If you explicitly delete table-by-table, though, at least you (should) know what you're doing.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
cjduncan
post Jun 25 2019, 02:40 PM
Post#7



Posts: 32
Joined: 30-April 19



ADezii,

Thank you. I have both IR and the Cascade Delete Related Records enforced. A couple follow-up questions if you could answer them please:

1. In your Demo Query Definition, do I duplicate that string for each table?
2. In the string, where it references the ID, how do I get it to reference the ID on the form?
Go to the top of the page
 
ADezii
post Jun 25 2019, 02:55 PM
Post#8



Posts: 2,457
Joined: 4-February 07
From: USA, Florida, Delray Beach


You are only Deleting a Single Record in the SOI Table, RI + Cascade Delete will do the rest. As for referencing the Form:
CODE
Dim strSQL As String

'If Numeric
strSQL = "DELETE * FROM Suppliers WHERE Suppliers.[ID] = " & Forms![<Your Form>]![<Your Field>]


'If String
strSQL = "DELETE * FROM Suppliers WHERE Suppliers.[ID] = '" & Forms![<Your Form>]![<Your Field>] & "'"

CurrentDB.Execute strSQL, dbFailOnError
Go to the top of the page
 
isladogs
post Jun 25 2019, 03:33 PM
Post#9


UtterAccess VIP
Posts: 1,451
Joined: 4-June 18
From: Somerset, UK


Just to clarify a point about Cascade Delete & RI.
As adezii rightly pointed out deleting records from the table on the one side of the join will also delete related records from the tables(s) on the many side of the join.

However, the converse is not true.
Deleting records form the table on the many side of the join has no effect on the other table(s) on the 'one side'
So using the query in post #1, records cannot be deleted from several tables
The delete query would need to be based on the SOI table NOT the SOIM2OBJ table

BTW the query in post 1 wouldn't work anyway as it has no FROM clause

I have an extended article on my website, part of which may be useful for this topic - Relationships and Referential Integrity

--------------------
Go to the top of the page
 
cjduncan
post Jun 25 2019, 04:37 PM
Post#10



Posts: 32
Joined: 30-April 19



Thank you so much!! Worked perfectly!
Go to the top of the page
 
ADezii
post Jun 25 2019, 05:32 PM
Post#11



Posts: 2,457
Joined: 4-February 07
From: USA, Florida, Delray Beach


Please ignore this Post, written in error.
This post has been edited by ADezii: Jun 25 2019, 06:21 PM
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    16th July 2019 - 03:32 PM