Cascade Delete with Junction Table

[Chris Smith]

Hello.

I have a many-to-many relationship using table A and table C. Table B is my
junction table.

A <-> B <-> C

I am able to delete a record from A and have the related records from B
delete, also.

I am able to delete a record from C and have the related records from B
delete, also.

What I would like to have happen is: delete a record from A and have the
related records from B *AND* C be deleted. Also, I would like to delete a
record from C and have the related records from B *AND* A be deleted.

Can this be done through relationships? Is there any non-VBA way to
accomplish this?

Thank you for your time!

Chris Smith

 

[Allen Browne]

You cannot do that with relationships, becuase there is no direct
relationship between tables A and C.

Taking Northwind as an example, we will use the Orders as A, [Order Details]
as B, and Products as C.

When you delete an order, it makes sense to delete the Order Details as
well. But what would it mean to delete all the products in the related Order
Details? What would you expect to happen to *other* orders that also refer
to those products?

 

[Chris Smith]

Hi, Allen:

Thanks for taking the time to reply.

Perhaps it would help if I explained the logic behind my program. I am
making a Contact database filled with people who are selling their homes on
their own.

My table A is really my [Addresses] table.

My table C is really my [Phone Numbers] table

Table B is my junction.

I know your next question: Why wouldn't you put this information together in
the same table?

The answer is: Because I might not know both pieces of information at the
same time; I might never know both pieces. If the info was in the same table
there would be a lot of wasted spaces due to empty fields. I'm trying to
avoid that.

I need a table of Numbers for when I will contact the seller by phone (when
that's all that I have). I need another table for addresses to which I will
send a letter. When I discover that a Number that is known to me is
associated with an Address that I've just discovered, I need to relate these
two pieces of info in my junction table.

For example, I read in the newspaper that a home is for sale. The Phone
Number is in the ad but the street address is not. So, I record the Phone
Number and call the person after I'm done collecting phone numbers for the
day. Days later, I drive by a home that has a For Sale sign in front of it.
On the sign is a Phone Number and I know the street address because I'm right
in front of the house. Now, I take these two pieces of information home with
me and enter them into my Access form. Since the street address was not
previously known to me, my program writes it to the [Addresses] table. When
the program processes the Phone Number, it detects that the Number was
already known to me and must now associate that Number with the new Address
(in my junction table). Since I already called the seller on the phone days
ago, this functionality will save me the expense of creating a letter and
paying postage for a seller I've already contacted.

This senario could happen vice-versa, also. I could know the address first
and discover the Phone Number days later.

Also, I might have an Address and NEVER discover the Phone Number and
vice-versa.

I need to be able to delete an Address and have the Phone Number and
junction record be deleted, also. And I need to be able to delete a Phone
Number and have the other two records delete with it.

Any ideas? Thanks!

Chris Smith

You cannot do that with relationships, becuase there is no direct
relationship between tables A and C.

Taking Northwind as an example, we will use the Orders as A, [Order Details]
as B, and Products as C.

When you delete an order, it makes sense to delete the Order Details as
well. But what would it mean to delete all the products in the related Order
Details? What would you expect to happen to *other* orders that also refer
to those products?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hello.

I have a many-to-many relationship using table A and table C. Table B is
my
junction table.

A <-> B <-> C

I am able to delete a record from A and have the related records from B
delete, also.

I am able to delete a record from C and have the related records from B
delete, also.

What I would like to have happen is: delete a record from A and have the
related records from B *AND* C be deleted. Also, I would like to delete a
record from C and have the related records from B *AND* A be deleted.

Can this be done through relationships? Is there any non-VBA way to
accomplish this?

Thank you for your time!

Chris Smith

 

[Allen Browne]

Assuming you have cascading deletes in both directions, you could execute a
Delete query on the other primary table where there is a matching record in
the junction table, and then delete from the table you started with.

This example assumes you have the form open for the phone numbers, and you
want to delete the addresses associated with the PhoneNum field in the form:

Dim strSql As String
If Me.Dirty Then Me.Undo
If Not Me.NewRecord Then
strSql = "DELETE FROM [Addresses] WHERE EXISTS (SELECT .[ID]
FROM WHERE .[PhoneNum] = """ & Me.[txtPhoneNum] & """);"
With dbEngine(0)(0)
.Execute strSql, dbFailOnError
'Debug.Print db.RecordsAffected & " address(es) deleted."
End With
RunCommand acCmdDeleteRecord
End If

The code assumes the PhoneNum is a Text type field. Lose the extra quotes if
it is a Number type field.

The strSql string is all one line. It uses a subquery to identify the
address(es) to delete. If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

One consideration is that the addresses and junction record(s) will be
deleted, even if the user answers No the the confirmation dialog for the
delete.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi, Allen:

Thanks for taking the time to reply.

Perhaps it would help if I explained the logic behind my program. I am
making a Contact database filled with people who are selling their homes
on
their own.

My table A is really my [Addresses] table.

My table C is really my [Phone Numbers] table

Table B is my junction.

I know your next question: Why wouldn't you put this information together
in
the same table?

The answer is: Because I might not know both pieces of information at the
same time; I might never know both pieces. If the info was in the same
table
there would be a lot of wasted spaces due to empty fields. I'm trying to
avoid that.

I need a table of Numbers for when I will contact the seller by phone
(when
that's all that I have). I need another table for addresses to which I
will
send a letter. When I discover that a Number that is known to me is
associated with an Address that I've just discovered, I need to relate
these
two pieces of info in my junction table.

For example, I read in the newspaper that a home is for sale. The Phone
Number is in the ad but the street address is not. So, I record the Phone
Number and call the person after I'm done collecting phone numbers for the
day. Days later, I drive by a home that has a For Sale sign in front of
it.
On the sign is a Phone Number and I know the street address because I'm
right
in front of the house. Now, I take these two pieces of information home
with
me and enter them into my Access form. Since the street address was not
previously known to me, my program writes it to the [Addresses] table.
When
the program processes the Phone Number, it detects that the Number was
already known to me and must now associate that Number with the new
Address
(in my junction table). Since I already called the seller on the phone
days
ago, this functionality will save me the expense of creating a letter and
paying postage for a seller I've already contacted.

This senario could happen vice-versa, also. I could know the address first
and discover the Phone Number days later.

Also, I might have an Address and NEVER discover the Phone Number and
vice-versa.

I need to be able to delete an Address and have the Phone Number and
junction record be deleted, also. And I need to be able to delete a Phone
Number and have the other two records delete with it.

Any ideas? Thanks!

Chris Smith

You cannot do that with relationships, becuase there is no direct
relationship between tables A and C.

Taking Northwind as an example, we will use the Orders as A, [Order
Details]
as B, and Products as C.

When you delete an order, it makes sense to delete the Order Details as
well. But what would it mean to delete all the products in the related
Order
Details? What would you expect to happen to *other* orders that also
refer
to those products?

Hello.

I have a many-to-many relationship using table A and table C. Table B
is
my
junction table.

A <-> B <-> C

I am able to delete a record from A and have the related records from B
delete, also.

I am able to delete a record from C and have the related records from B
delete, also.

What I would like to have happen is: delete a record from A and have
the
related records from B *AND* C be deleted. Also, I would like to delete
a
record from C and have the related records from B *AND* A be deleted.

Can this be done through relationships? Is there any non-VBA way to
accomplish this?

Thank you for your time!

Chris Smith

 

[Chris Smith]

Thank you, Allen!

Your reply led me in the right direction. As soon as I read the word
"subqueries" in your reply, my mind flashed back to my Access 97 Programming
for Dummies book (yes, it's still on my shelf!). Through that book I was
introduced to subqueries but never used them. Since that was so long ago I
NEVER would have thought to use them. Thank you for directing me there.

I found that I needed to use the IN operator ('97 for Dummies) as opposed to
EXISTS. I also needed to use nested subqueries.

For anyone following this post, here's the solution I will be using:

Private Sub Test()

' Address is known. Delete Junction records and related Phone records
first, then delete Address record:

With DoCmd
.RunSQL "DELETE * FROM [PhoneNumbers] WHERE [PhoneNumberID] IN
(SELECT [PhoneNumberID] FROM [Address-Phone Junction] WHERE [AddressID] IN
(SELECT [AddressID] FROM [Addresses] WHERE [Address] = '123 Main Street'))"
.RunSQL "DELETE * FROM [Addresses] WHERE [Address] = '123 Main
Street'"
End With

' Phone Number is known. Delete Junction records and related Address
records first, then delete Phone record:

With DoCmd
.RunSQL "DELETE * FROM [Addresses] WHERE [AddressID] IN (SELECT
[AddressID] FROM [Address-Phone Junction] WHERE [PhoneNumberID] IN (SELECT
[PhoneNumberID] FROM [PhoneNumbers] WHERE [PhoneNumber] = '7325551212'))"
.RunSQL "DELETE * FROM [PhoneNumbers] WHERE [PhoneNumber] =
'7325551212'"
End With

End Sub

There might be an easier way to do it, but this works for me.

Thanks so much for your time. I certainly appreciate it.

Chris Smith

Assuming you have cascading deletes in both directions, you could execute a
Delete query on the other primary table where there is a matching record in
the junction table, and then delete from the table you started with.

This example assumes you have the form open for the phone numbers, and you
want to delete the addresses associated with the PhoneNum field in the form:

Dim strSql As String
If Me.Dirty Then Me.Undo
If Not Me.NewRecord Then
strSql = "DELETE FROM [Addresses] WHERE EXISTS (SELECT .[ID]
FROM WHERE .[PhoneNum] = """ & Me.[txtPhoneNum] & """);"
With dbEngine(0)(0)
.Execute strSql, dbFailOnError
'Debug.Print db.RecordsAffected & " address(es) deleted."
End With
RunCommand acCmdDeleteRecord
End If

The code assumes the PhoneNum is a Text type field. Lose the extra quotes if
it is a Number type field.

The strSql string is all one line. It uses a subquery to identify the
address(es) to delete. If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

One consideration is that the addresses and junction record(s) will be
deleted, even if the user answers No the the confirmation dialog for the
delete.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi, Allen:

Thanks for taking the time to reply.

Perhaps it would help if I explained the logic behind my program. I am
making a Contact database filled with people who are selling their homes
on
their own.

My table A is really my [Addresses] table.

My table C is really my [Phone Numbers] table

Table B is my junction.

I know your next question: Why wouldn't you put this information together
in
the same table?

The answer is: Because I might not know both pieces of information at the
same time; I might never know both pieces. If the info was in the same
table
there would be a lot of wasted spaces due to empty fields. I'm trying to
avoid that.

I need a table of Numbers for when I will contact the seller by phone
(when
that's all that I have). I need another table for addresses to which I
will
send a letter. When I discover that a Number that is known to me is
associated with an Address that I've just discovered, I need to relate
these
two pieces of info in my junction table.

For example, I read in the newspaper that a home is for sale. The Phone
Number is in the ad but the street address is not. So, I record the Phone
Number and call the person after I'm done collecting phone numbers for the
day. Days later, I drive by a home that has a For Sale sign in front of
it.
On the sign is a Phone Number and I know the street address because I'm
right
in front of the house. Now, I take these two pieces of information home
with
me and enter them into my Access form. Since the street address was not
previously known to me, my program writes it to the [Addresses] table.
When
the program processes the Phone Number, it detects that the Number was
already known to me and must now associate that Number with the new
Address
(in my junction table). Since I already called the seller on the phone
days
ago, this functionality will save me the expense of creating a letter and
paying postage for a seller I've already contacted.

This senario could happen vice-versa, also. I could know the address first
and discover the Phone Number days later.

Also, I might have an Address and NEVER discover the Phone Number and
vice-versa.

I need to be able to delete an Address and have the Phone Number and
junction record be deleted, also. And I need to be able to delete a Phone
Number and have the other two records delete with it.

Any ideas? Thanks!

Chris Smith

You cannot do that with relationships, becuase there is no direct
relationship between tables A and C.

Taking Northwind as an example, we will use the Orders as A, [Order
Details]
as B, and Products as C.

When you delete an order, it makes sense to delete the Order Details as
well. But what would it mean to delete all the products in the related
Order
Details? What would you expect to happen to *other* orders that also
refer
to those products?

Hello.

I have a many-to-many relationship using table A and table C. Table B
is
my
junction table.

A <-> B <-> C

I am able to delete a record from A and have the related records from B
delete, also.

I am able to delete a record from C and have the related records from B
delete, also.

What I would like to have happen is: delete a record from A and have
the
related records from B *AND* C be deleted. Also, I would like to delete
a
record from C and have the related records from B *AND* A be deleted.

Can this be done through relationships? Is there any non-VBA way to
accomplish this?

Thank you for your time!

Chris Smith

 

[Tony Toews]

I know your next question: Why wouldn't you put this information together in
the same table?

The answer is: Because I might not know both pieces of information at the
same time; I might never know both pieces. If the info was in the same table
there would be a lot of wasted spaces due to empty fields. I'm trying to
avoid that.

Empty fields don't take any space in Access tables. Well, they might
take a byte as an end of field marker.

I need a table of Numbers for when I will contact the seller by phone (when
that's all that I have). I need another table for addresses to which I will
send a letter. When I discover that a Number that is known to me is
associated with an Address that I've just discovered, I need to relate these
two pieces of info in my junction table.

But here you have excellent reasons for keeping address and phone
number in separate tables.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Chris Smith]

Hi Tony,

Thanks for your reply.

I was always under the impression that empty fields wasted 'significant'
space. I've always made an effort to design my tables in a way that would
avoid empty fields.

I'll research this further. I guess I'll need to change my paradigm.

 

[Tony Toews]

I was always under the impression that empty fields wasted 'significant'
space. I've always made an effort to design my tables in a way that would
avoid empty fields.

That certainly was the case in the past database schemas, or systems
with no true database, but certainly not in Access. Although, now
that I think about it I'm not so sure this is the case for numeric
data.

(Working on the IBM 5110 computer and S/34 and S/36 mini computer we
created tables with records of a fixed length and then allocated fixed
length fields for text strings. But that was in the late 70s and
80s.)

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm