Can a Query with 3 Tables be updatable?

  • Thread starter Thread starter croy
  • Start date Start date
C

croy

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?
 
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.
 
Why should you ever need to edit queries that have more than a couple tables?
Click to expand...


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.
Click to expand...

Yup.

I hope this makes sense--I'm fading fast.
 
What about my comment about primary and foreign key fields in the joins? Did
you understand this or just forget to respond?

--
Duane Hookom
Microsoft Access MVP


croy said:
Why should you ever need to edit queries that have more than a couple tables?
Click to expand...


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.
Click to expand...

Yup.

I hope this makes sense--I'm fading fast.
Click to expand...
 
What about my comment about primary and foreign key fields in the joins? Did
you understand this or just forget to respond?
Click to expand...


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?
 
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?
 
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?
Click to expand...


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.
 
I'm going to create a test version of this whole mdb, and
see how it goes if I make this change.
Click to expand...


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!
 
Back
Top