Can a Query with 3 Tables be updatable?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

croy - 05 Nov 2007 21:44 GMT

Any time I put more than two tables in a query, it will not
allow the addition of new records. Are others getting
different results?

Signature

Thanks,
croy

Reply to this Message

Duane Hookom - 05 Nov 2007 22:33 GMT

Why should you ever need to edit queries that have more than a couple tables?
Using forms with subforms and combo boxes generally limits my forms to record
sources with only a single table.

If you have more tables, they must be linked based on primary and foreign
key fields.

Signature

Duane Hookom
Microsoft Access MVP

> Any time I put more than two tables in a query, it will not
> allow the addition of new records. Are others getting
> different results?

Reply to this Message

croy - 06 Nov 2007 04:10 GMT

>Why should you ever need to edit queries that have more than a couple tables?

Thanks for the reply...

Well... I have a continuous subform (for data entry)
formatted like a datasheet, with a couple of buttons on each
row for opening popup forms that hold related data.

It would be very nice for data-entry folks to be able to see
which rows in the subform have any associated records in the
related tables populated by the popup forms, without having
to pop the forms open. This comes in handy for checks and
edits (and may have to add a skipped record here and there),
and making sure that the data you're putting in hasn't been
done already, etc.

I changed the form-launching buttons to textboxes with
OnClick code, so I could format them. Then I added the
tables behind the popup forms to the query behind the
subform with outer joins. In the query grid, I put
"IIf(IsNull([Popup1Id]),"","DataExists") statements so that
I'd have something for the "buttons'" conditional formatting
to chew on.

When I do that for just one of the popup buttons, all is
well. But when I add the other table for the 2nd popup
button, I can not add records to the query (or the subform).

Marshall Barton was coaching me on this, and he said he has
no problem updating queries with three tables involved.

Today I made a test mdb as simple as I could make it, with
three related tables. I could not find any way to get all
three tables into a query, and still be able to add records
to it. Hence my question above.

>Using forms with subforms and combo boxes generally limits my forms to record
>sources with only a single table.
>
>If you have more tables, they must be linked based on primary and foreign
>key fields.

Yup.

I hope this makes sense--I'm fading fast.

Signature

croy

Reply to this Message

Duane Hookom - 06 Nov 2007 04:30 GMT

What about my comment about primary and foreign key fields in the joins? Did
you understand this or just forget to respond?

Signature

Duane Hookom
Microsoft Access MVP

> >Why should you ever need to edit queries that have more than a couple tables?
>
[quoted text clipped - 41 lines]
>
> I hope this makes sense--I'm fading fast.

Reply to this Message

croy - 06 Nov 2007 14:43 GMT

>What about my comment about primary and foreign key fields in the joins? Did
>you understand this or just forget to respond?

Apparently I replied, but only silently, and to myself (Not
very helpful)!

I can't remember the last time I built a table without a PK.
In my test mdb, for example, all three tables have PKs and
the two detail tables are related to the master by Foreign
Keys with the same values as the master table's PK. All
relationships are set in the Relationships window, with
Referential Integrity enforced, and Cascading Updates and
Cascading Deletes set.

Is that what you were asking, or did I miss your point
entirely?

Signature

croy

Reply to this Message

Duane Hookom - 06 Nov 2007 15:31 GMT

Are the tables related as:

Table1.PK 1-M Table2.FK
and
Table2.PK 1-M Table3.FK

or
Table1.PK 1-M Table2.FK
and
Table1.PK 1-M Table3.FK

In other words, is Table1 related to both Table2 and Table3?

Signature

Duane Hookom
Microsoft Access MVP

> >What about my comment about primary and foreign key fields in the joins? Did
> >you understand this or just forget to respond?
[quoted text clipped - 12 lines]
> Is that what you were asking, or did I miss your point
> entirely?

Reply to this Message

croy - 06 Nov 2007 16:39 GMT

>Are the tables related as:
>
[quoted text clipped - 8 lines]
>
>In other words, is Table1 related to both Table2 and Table3?

The latter.

This seems to be the rub when it come to my attempts to
wring the conditional formatting I want out of my query/form
setup. I was just doing some more testing, and discovered
that if I change the relationships, such that Table1 and
Table2 are related, and Table 2 and Table 3 are related,
then I can have all three in one query and still be able to
add records.

What's even more interesting, I think that the relationships
*could* be arranged that way, Table2 and Table3 aren't
necessarily related, although there shouldn't be any records
in Table3 if there aren't any in Table2. Hmmm. The more I
think about it, the more I think they *are* related.

How sobering to have a problem with conditional formatting
show me that my normalization isn't optimized!

I'm going to create a test version of this whole mdb, and
see how it goes if I make this change.

Thanks for the prod.

Signature

croy

Reply to this Message

croy - 06 Nov 2007 20:31 GMT

>I'm going to create a test version of this whole mdb, and
>see how it goes if I make this change.

Well, it was a nice thought, but I'm now conviced that
conditional formatting, driven by "off-form" data, is just
not something that I can make happen the way I wanted.
Either I get a recordset that can't be updated, or a subform
with duplicate rows (which I can't stomach).

I tried putting a DCount statement in the Conditional
Formatting dialog, and couldn't get that to work either.

The data-entry folks are gonna have to live with it, or hire
a *real* database monger!

Signature

croy

Reply to this Message