Query vs form with child & parent: Deleting rows

[Guest]

When I delete a row from a query involving both parent and child rows, what
determines which rows are deleted from the source tables?

When I display a joining query in datasheet mode and manually delete one of
the rows, only the record from the child table deleted, as I intended. But
when I display this query in a form and delete a row from there, it deletes
the source record from both the child and the parent tables. It doesn't seem
to make any difference whether the join is inner or outer, nor even whether I
display parent data in the form. I've been trying for a while to figure out
exactly why it happens this way with this particular form and no others, and
I'm whipped. Anyone know the rules for this sort of thing?

I'm not sure what details to specify, so I'll wait for some helpful soul to
ask.

 

[Albert D. Kallal]

There is two issue here.

First, if you set cascade deletes in the relationship window, then that is
#1 defining issue here.

If you delete a parent record, and have 5 related tables to that main
record, all of the child records in all of those tables will delete
automatically (assume you set the cascade deletes in eh relationship
window). These child records will delete even if you just delete main
record in a query. In fact, the setup of the query WILL NOT EFFECT this
behaviours. they will all delete automatic. and, you can delete the main
record directly from the table, or delete his record in some query. So, it
how you setup the relationships.

When I display a joining query in datasheet mode and manually delete one
of
the rows, only the record from the child table deleted, as I intended.

yes, if you base table is the child record, and you join in other records,
even the parent table..deleting this record will NOT delete the main
record. However, you have to be carefully, since often when you drop in a
main table, the query builder flips what table is the "base" table (the
first table you dropped in).

But
when I display this query in a form and delete a row from there, it
deletes
the source record from both the child and the parent tables.

It must mean that the form is based on the main table, and the child table
is joined in.

It doesn't seem
to make any difference whether the join is inner or outer, nor even
whether I
display parent data in the form.

Well, you have to clarify what you mean by parent data. You have a form
bound to a query with 50 fields, but only place 2 fields on the form...this
issue would NOT effect the deleting. (so, the fact that you "display"
or not "display" parent data is not a issue here. If you mean *include*
in the query..that is a different matter. It would not matter if you include
1, or 50 fields, the defining issue would be did you include the table at
all in the query).

The bottom line is that if you delete a record, you are deleting that
record. if there is cascade deletes, then they are ALWAYS DONE.

So, the ONLY confusing part here is that is the form based on the child
record ONLY, or did we miss-match this, and the query is actually based on
the MAIN table (parent table), but we think it is a query that displays both
parent + child. As I said, most of the time when you build a query, you work
from the top most table down (so, if you join in 2, or even 6 tables...you
start at the top most).

Also, the direction in which you draw the join line will very much effect
this.

If you delete a main record, the overriding issue is if you setup the
relations, and have cascade delete...it will always cascade delete records
of what tables you include in the actual query....

However, if you build a query that is based on the child record, and
it joins in the parent record...you delete this record via that query,
then ONLY the child record will delete. It going to be how your
delete query looks. If you are finding that deleting this record
causes the parent record to delete, then the query is based
on that parent table, and you just wrong about what table the
query is based on.

Setting up your relationships correct is very important..and can a
reveal a large number of things about the design of the application.
What follows is a repost of mien
on this issue:

-------------

A left join means that a query will return the "parent" records when the
child table HAS NO correspond record.

So, if we have Customers, and Invoices tables, a left join would give us:

CustomerName InvoiceNumber
AppleBee
Donought Shop 1234
Doughnut Shop 1344

Note how AppleBee does NOT yet have a invoice number in the invoices
table..but the query still returns the record. You have to use left joins
for lookup values when you drop in many tables (can't use standard joins in
this case).

So, with a left join, the corresponding child record DOES NOT have to exist.
Just think of "left" side can exist...but the right side does NOT have to !

A middle join, or so called inner join is the standard join, and BOTH tables
have to have a value for the join. The above would produce:

CustomerName InvoiceNumber
Dounought Shop 1234
Doughutn Ship 1344

So, in the above inner join, our customer name of Applebee does not show,
since that customer does NOT yet have a invoice record in the invoice table.

To make a left join, you drop in the tables (in the query builder, or the
relationship designer), and draw the join line to the appropriate filed
between each table. You then double click on the join line. You then click
on the join type button

You get three options:

Only include rows where the joined fields from both tables are equal
(this standard default inner join)

Include ALL records from "Customers" and only those records from
"Invoices" where the joined fields are equal

(this is our left join. So, our main table Customers will be returned in
this query, REGARDLESS if the child records (invoices in this example)
exist, or not!. This is left join

Include ALL records from "Invoices" and only those records from
"Customers" where the joined fields are equal
This sis obviously a right join....

For forms, and sub-forms, and related tables, left joins are quite
important.

If you look at the following screen shot, you can see that most relations
ships are this left join, and RI is enforced.

http://www.members.shaw.ca/AlbertKallal/Articles/PickSql/Appendex2.html

tblBgroup (booking group) for example may, or may not have payments made
(tblPayments). Thus, you can add a booking group, and NOT have to add child
records. However, full RI is enforced, and you can see the side ways 8
"omega" sign AND THE ARROW HEAD. The simple lookup fields are simply just a
arrow drawn, and no "1", or omega sign exists (tblPayments to tblHowpaid for
example is a simple lookup). It is GREAT that I can look at the ER diagram,
and instantly know if child records are required, or they are not!!

The tables that MUST have a child records can also clearly be seen. If you
go from the tblBgroup to the its parent table, you will see table
tblBooking. You can easily see that there is a 1 to many here also, but NO
ARROW head exists. Thus, when I create a booking, my designs will ALWAYS
ASSUME that a child records in tblBgroup (booking group) will exist (ie: I
must code, and assume that when I add a tblBooking records, my code also
assumes that a tblBGroup will also have to be added). In plain English this
means that when I make a booking (reservation), my code assumes that
you MUST have people in that booking. However, I most certainly allow
people to be booked, but not yet have made any payments. So, your
relationship(s) if done right should reflect the rules you as a developer
want to maintain. I should point out that a left join, or a standard
(inner join) both allow child records to NOT exist, but you still
should correctly set this relationship, since when it comes to making
reports, and writing code...I will know what my assumptions
were at the time (ie: do I HAVE to add those child records
for the software to function correctly. So, if I write code to
make a booking, all of my code thus assumes that people
are also to be added to the booking. Break that assuming
of mine, and likely my code will break).

So, the ER diagram can convey a lot about your designs. Down the road, I can
now look at that diagram, and when writing code, I will know if the design
can, and does assume if child records are required. If you look at that
table, it is VERY RARE that I require the child record. That application has
about 60 tables, and I think only 1 or 2 in the whole thing is NOT a left
join. Hence, you most certainly should set the relation in the window for
future reference, and also it will help you when you create a query, or a
report.

 

[Guest]

First, I have not set up any relationships at all in this database. I
understand that it's recommended, and why, and I expect that I will someday.
For now, though, I can be sure cascading deletes are not an issue because I
deliberately omitted all automatic relationships.

Second, I forgot to say at the outset last time that I'm sort of a newbie at
this. I've been writing in various computer languages professionally for
decades, and I've been tweaking this database in VBA for a year or so -- but
I never took a class in VBA, I had to learn it from manuals, and mostly when
I wanted to do something that I couldn't figure out, I just kept whacking
away until I found something that worked. Therefore I'm quite sure I've
taught myself a few techniques that are either stupidly inefficient or likely
to cause problems. Don't assume I'm writing my code sanely, in other words.

Now, you seem to be saying (correct me if I'm mistaken) that it makes a
difference which table I name first in my join. That doesn't accord with my
experience, which is why I think I may be misreading you. I'll show you the
actual query so you can see what ~I~ think is happening.

The parent Table is called "Who"; each record represent either a company, or
an individual who works for one of those companies. In my application I
display the Who records in a continuous main form called [Company list]. In
that form's detail section each record has a button I can push that opens a
second form (not a sub-form) displaying any Who records that might be
subordinate to that record, that is, all individuals (and sometimes child
companies) whose Owner field contains the record key of the parent company.
That second form (named clCoSub) is fed by this query:

SELECT *
FROM Who AS parent INNER JOIN Who AS child ON child.Owner=parent.ID
WHERE child.Owner=Forms![Company List]!ID

No problem so far. But I have a child table called "Channel"; each record
there is an email address, a phone number, a URL or some other way of
contacting the company or individual represented by its parent Who record.
In clCoSub's detail section is a button labeled "Channels", which displays a
third form (named clCoSubChan) that is fed by this query:

SELECT *
FROM Channel AS c LEFT JOIN Who AS w ON w.ID=c.Owner
WHERE c.Owner In (Forms!clCoSub!CoID,Forms!clCoSub!ID)

This query displays every channel that is owned either by the individual
whose button I clicked (clCoSub!ID), or the company (clCoSub!CoID) to which
that individual is subordinate. Let's pretend this query displays five
records:

Company X : Fax number
Company X : URL
Bob Barcy : Office phone
Bob Barcy : Cell number
Bob Barcy : Email address

The form displays the correct five channels. But if in that form I delete,
say, Bob Barcy's cell number, Access deletes not just that Channel record but
also the parent -- Bob Barcy's Who record -- leaving Barcy's office phone and
email address orphaned. (I know, setting relationships for referential
integrity would delete the two orphaned records, but let's not lose sight of
the ball; what I want to know is why Access is deleting Barcy's Who record).

I'd expect that since the form acts that way, the query would do the same --
that is, if I display the query in Datasheet mode and delete the record for
Barcy's cell phone, it'll act the same way as in the form. But no, when I
delete that record in the query's datasheet display, it deletes just the
Channel record. That's what I want the form to do, too.

Furthermore, it all acts correctly in clCoSub. Remember that form? It
displays Who records that are subordinate to another Who record; if in that
query I delete a record, Access deletes just the child Who record, not the
parent Who record -- AND LIKEWISE IN THE FORM!, that is, I can delete a child
record in clCoSub and lose just the child Who record and keep the parent Who
record, which is what I wanted in the first place. So why doesn't it work
that way in clCoSubChan?

You spoke of "main table" in a join, and seemed to say that it's the table
you mention first, whether your join is inner or outer. But I already tried
varying the JOINs in that way and it didn't make any difference. If I didn't
misunderstand you, how sure are you of that theory? You sound pretty
certain, but it isn't working that way at this end.

 

[Albert D. Kallal]

First, I have not set up any relationships at all in this database. I
understand that it's recommended, and why, and I expect that I will
someday.
For now, though, I can be sure cascading deletes are not an issue because
I
deliberately omitted all automatic relationships.

I am not sure what you mean by "automatic" relationships.

I would bring up the relationships window, and
then form the menu option choose:

Relationships->show all

Remember, relationships can be setup, but they don't necessary
display in the relationship window.

Now, you seem to be saying (correct me if I'm mistaken) that it makes a
difference which table I name first in my join. That doesn't accord with
my
experience, which is why I think I may be misreading you. I'll show you
the
actual query so you can see what ~I~ think is happening.

No, what I am saying is that if you use the query builder, and drop in two
tables, which table is consider the base table??? So, it not a case of
which table you name first in the join, it is a case of what table the
select statement will be based on. Often, a left join is considered the same
as a right join, but the tables flipped.

SELECT *
FROM Who AS parent INNER JOIN Who AS child ON child.Owner=parent.ID
WHERE child.Owner=Forms![Company List]!ID

Ok..the above base table is Who. However, we could have thoen in the child
table first, and joined it to the parent table "who"...the query would look
the
same results wise. (so, that was my original point -- it seems quite clear
that you have these queries correct, and the query builder is not fooling
you).

Company X : Fax number
Company X : URL
Bob Barcy : Office phone
Bob Barcy : Cell number
Bob Barcy : Email address

The form displays the correct five channels. But if in that form I
delete,
say, Bob Barcy's cell number, Access deletes not just that Channel record
but
also the parent -- Bob Barcy's Who record

Hum...

I would double check the forms record source. are you 100% positive it is
based on that query, or does the form have its own sql data source (when
you click on a forms data source..you are often asked to invoice the query
builder).

I'd expect that since the form acts that way, the query would do the
same --
that is, if I display the query in Datasheet mode and delete the record
for
Barcy's cell phone, it'll act the same way as in the form. But no, when I
delete that record in the query's datasheet display, it deletes just the
Channel record. That's what I want the form to do, too.

I have to think that something is set in that form. Perhaps there is a
control
on the form that is bound to the primary key id of the wrong table?

You spoke of "main table" in a join, and seemed to say that it's the table
you mention first, whether your join is inner or outer.

As I said, we talking about what table the query is based on. many quires
can produce the same result if you flip the join around, and base the query
on the child table, and join in the parent table. So, I was NOT talking
about
the join command, and flipping the order of the join command (you can
put either expression on either side of the join...not talking about that -
and that should not make a difference here anyway).

I suspect that you have control on the form that is the primary key of the
parent form. The fact that query allows deleting of the child record, but
the same query bound to a form does not has to mean that the form is
not using the same query, or something is confusing ms-access as to what
record should be deleted. I would check for a control on the form that
has a common name between each table...that might explain this...

I would also consider using the wizard to build a new "test" form. (and,
base it on that query). See if deleting a record from that test form also
deletes the parent record? (and, while we are at this...I assume you are
using the built in deleting option in the ms-access form...not some custom
delete code?)

And, the other thing I would check if there is any sub-forms in use in that
form (you hint not..but, I would check the link child/master settings in the
form if you do have any sub-forms in that problem form.

right off the top of my head, deleting a child record inn a form should not
delete the parent. It might be one of those "features" that don't make
sense..but, I not heard of this.

I just tried building a query that was based on the child table..but joined
in the parent records. Deleting this record in the form did NOT delete the
parent record (and, no actually relationships existed in this test either).
So, whatever is happening in your case is not the norm. I did notice that
building the form with wizard on this query actually RE-WROTE the query for
me..and the resulting form was not based on eh query..but some sql written
that *looks* to that query as the base table.