JOIN query: What happens when you delete a row?

[Bob Bridges]

Start with two tables, parent records in one and child records in the other,
a one-to-many relationship. Create a select statement joining the two.
Display the query in datasheet mode. When I delete a row, only the child
record is deleted from the source tables; the parent record is still
there...which is what I wanted.

Now display fields from that query in a continuous form. When I delete a
record from that form, one of the child records AND THE PARENT record are
deleted -- definitely not what I wanted.

I have another query that could be described the same way, except in that
form when I delete a record only the child record is deleted from the
original tables. It doesn't seem to make any difference whether the join is
inner or outer, nor whether parent data is displayed in the detail section.

So here's the question: What determines the outcome? How do I write the
query, and/or design the form, to be sure that when I want to delete a child
record in the form, the parent record will survive? Are the rules for this
documented somewhere?

---
Bob Bridges, rh*****@attglob al.net, cell 336 382-7313
(hotel) 612 869-7704 xt 118, fax [phone]

/* A crooked politician may be faithful to his wife, but a man who deceives
his own wife can't be relied on to deal honestly with the public. We have
been getting this backward lately. We've been assuming that you can have
public virtue without private virtue. -Joseph Sobran, 1998 */

[tina]

if you are able to delete a parent record and leave *any* of its' child
records intact, then you haven't enforced referential integrity in your
table relationships. recommend you do that immediately.

hth
"Bob Bridges" <rh*****@attglo bal.netwrote in message
news:45e90dd7$1 @kcnews01...

Start with two tables, parent records in one and child records in the

other,

a one-to-many relationship. Create a select statement joining the two.
Display the query in datasheet mode. When I delete a row, only the child
record is deleted from the source tables; the parent record is still
there...which is what I wanted.

Now display fields from that query in a continuous form. When I delete a
record from that form, one of the child records AND THE PARENT record are
deleted -- definitely not what I wanted.

I have another query that could be described the same way, except in that
form when I delete a record only the child record is deleted from the
original tables. It doesn't seem to make any difference whether the join

is

inner or outer, nor whether parent data is displayed in the detail

section.

>
So here's the question: What determines the outcome? How do I write the
query, and/or design the form, to be sure that when I want to delete a

child

record in the form, the parent record will survive? Are the rules for

this

documented somewhere?

---
Bob Bridges, rh*****@attglob al.net, cell 336 382-7313
(hotel) 612 869-7704 xt 118, fax [phone]

/* A crooked politician may be faithful to his wife, but a man who

deceives

his own wife can't be relied on to deal honestly with the public. We have
been getting this backward lately. We've been assuming that you can have
public virtue without private virtue. -Joseph Sobran, 1998 */

[Bob Bridges]

Message received, but that's not my question. Hear it again, please: I
have a query with joined records, and when I hit the <Delkey while viewing
that data in a form I want it to delete just the child record, not the
parent. It works that way in one form but not another, and I haven't yet
been able to figure out what the difference is between the two so that I can
control it. Anyone know what makes that determination.

--- "tina" <no****@address .comwrote in message
news:t2******** **********@bgtn sc05-news.ops.worldn et.att.net...

if you are able to delete a parent record and leave *any* of its' child
records intact, then you haven't enforced referential integrity in your
table relationships. recommend you do that immediately.

--- "Bob Bridges" <rh*****@attglo bal.netwrote in message
news:45e90dd7$1 @kcnews01...

>Start with two tables, parent records in one and child records in the
other, a one-to-many relationship. Create a select statement joining the
two.
Display the query in datasheet mode. When I delete a row, only the child
record is deleted from the source tables; the parent record is still
there...whic h is what I wanted.

Now display fields from that query in a continuous form. When I delete a
record from that form, one of the child records AND THE PARENT record are
deleted -- definitely not what I wanted.

I have another query that could be described the same way, except in that
form when I delete a record only the child record is deleted from the
original tables. It doesn't seem to make any difference whether the join
is inner or outer, nor whether parent data is displayed in the detail
section.

So here's the question: What determines the outcome? How do I write the
query, and/or design the form, to be sure that when I want to delete a
child record in the form, the parent record will survive? Are the rules
for
this documented somewhere?

[Tom van Stiphout]

On Sat, 3 Mar 2007 12:12:29 -0600, "Bob Bridges"
<rh*****@attglo bal.netwrote:

It's tricky to delete rows using a query with a join. Just put
yourself in the position of the query processor. What are you supposed
to do?
Much better to use one on a single table. That's what most samples
(e.g. Northwind) show you.
I've never seen the rules you're looking for documented anywhere. If
you come up with some, please post back. In the meantime I would try
to redesign (e.g. with a parent form on the One table, and a details
subform on the Many table) to avoid the issue altogether.

-Tom.

>Message received, but that's not my question. Hear it again, please: I
have a query with joined records, and when I hit the <Delkey while viewing
that data in a form I want it to delete just the child record, not the
parent. It works that way in one form but not another, and I haven't yet
been able to figure out what the difference is between the two so that I can
control it. Anyone know what makes that determination.

--- "tina" <no****@address .comwrote in message
news:t2******* ***********@bgt nsc05-news.ops.worldn et.att.net...

>if you are able to delete a parent record and leave *any* of its' child
records intact, then you haven't enforced referential integrity in your
table relationships. recommend you do that immediately.

>--- "Bob Bridges" <rh*****@attglo bal.netwrote in message
news:45e90dd7$ 1@kcnews01...

>>Start with two tables, parent records in one and child records in the
other, a one-to-many relationship. Create a select statement joining the
two.
Display the query in datasheet mode. When I delete a row, only the child
record is deleted from the source tables; the parent record is still
there...whi ch is what I wanted.

Now display fields from that query in a continuous form. When I delete a
record from that form, one of the child records AND THE PARENT record are
deleted -- definitely not what I wanted.

I have another query that could be described the same way, except in that
form when I delete a record only the child record is deleted from the
original tables. It doesn't seem to make any difference whether the join
is inner or outer, nor whether parent data is displayed in the detail
section.

So here's the question: What determines the outcome? How do I write the
query, and/or design the form, to be sure that when I want to delete a
child record in the form, the parent record will survive? Are the rules
for
this documented somewhere?

[Bob Bridges]

Now this I can believe. I perfectly understand your point about seeing it
from the query processor's point of view. "Great!", I think, "Good advice;
I'll rewrite it to display...um... ."

Then I pause uncertainly. How DO I display data from multiple tables
without using a query joining two tables? If you're really saying simply
that my child form must not display any datum showing which parent it refers
to, I'm afraid that's out of court from the beginning. But I don't really
believe it's what you meant; you must have in mind something else, a
workaround that hasn't occurred to me.

So here's what I want my form to display, sort of:

Header section:
5047 Baxter Corp
5058 Tom Remak
Detail section:
Baxter Corp Fax number
Baxter Corp URL
Tom Remak Office phone
Tom Remak Email address
Tom Remak Cell phone

If you're saying I shouldn't display "Baxter Corp" or "Tom Remak" next to
the individual child records to identify whose they are, that's no good to
me and anyway I don't believe it -- that is, I can't believe MS would create
a product incapable of handling a situation that must be very common. But
if you're saying there's a way to structure the query and/or form so that
Access needn't be confused about which records to go after when I delete one
of the items in the above detail section, I'm all for it...but how do I feed
mixed data to this form without using a JOIN?

And meanwhile, as you suggest, if I figure it out for myself before one of
you bright folks clues me in, I definitely will post my solution. Thanks.

---
Bob Bridges, rh*****@attglob al.net, cell 336 382-7313
(hotel) 612 869-7704 xt 118, fax [phone]

/* A crooked politician may be faithful to his wife, but a man who deceives
his own wife can't be relied on to deal honestly with the public. We have
been getting this backward lately. We've been assuming that you can have
public virtue without private virtue. -Joseph Sobran, 1998 */

--- "Tom van Stiphout" <no************ *@cox.netwrote in message
news:h1******** *************** *********@4ax.c om...

It's tricky to delete rows using a query with a join. Just put
yourself in the position of the query processor. What are you supposed
to do?
Much better to use one on a single table. That's what most samples
(e.g. Northwind) show you.
I've never seen the rules you're looking for documented anywhere. If
you come up with some, please post back. In the meantime I would try
to redesign (e.g. with a parent form on the One table, and a details
subform on the Many table) to avoid the issue altogether.

>>--- "Bob Bridges" <rh*****@attglo bal.netwrote in message
news:45e90dd7 $1@kcnews01...
Start with two tables, parent records in one and child records in the
other, a one-to-many relationship. Create a select statement joining
the
two. Display the query in datasheet mode. When I delete a row, only
the child
record is deleted from the source tables; the parent record is still
there...whic h is what I wanted.

Now display fields from that query in a continuous form. When I delete
a
record from that form, one of the child records AND THE PARENT record
are
deleted -- definitely not what I wanted.

I have another query that could be described the same way, except in
that
form when I delete a record only the child record is deleted from the
original tables. It doesn't seem to make any difference whether the
join
is inner or outer, nor whether parent data is displayed in the detail
section.

So here's the question: What determines the outcome? How do I write
the
query, and/or design the form, to be sure that when I want to delete a
child record in the form, the parent record will survive? Are the
rules
for this documented somewhere?

[paii, Ron]

One work around to try.

Cancel the delete in the OnDelete event of the form, then delete the child
record with an action query.

"Bob Bridges" <rh*****@attglo bal.netwrote in message
news:45e90dd7$1 @kcnews01...

Start with two tables, parent records in one and child records in the

other,

a one-to-many relationship. Create a select statement joining the two.
Display the query in datasheet mode. When I delete a row, only the child
record is deleted from the source tables; the parent record is still
there...which is what I wanted.

Now display fields from that query in a continuous form. When I delete a
record from that form, one of the child records AND THE PARENT record are
deleted -- definitely not what I wanted.

I have another query that could be described the same way, except in that
form when I delete a record only the child record is deleted from the
original tables. It doesn't seem to make any difference whether the join

is

inner or outer, nor whether parent data is displayed in the detail

section.

>
So here's the question: What determines the outcome? How do I write the
query, and/or design the form, to be sure that when I want to delete a

child

record in the form, the parent record will survive? Are the rules for

this

documented somewhere?

---
Bob Bridges, rh*****@attglob al.net, cell 336 382-7313
(hotel) 612 869-7704 xt 118, fax [phone]

/* A crooked politician may be faithful to his wife, but a man who

deceives

his own wife can't be relied on to deal honestly with the public. We have
been getting this backward lately. We've been assuming that you can have
public virtue without private virtue. -Joseph Sobran, 1998 */