Why can't I update this query

I have tried:
Creating a new query
Compacting and repairing the database
Creating a new blank database, and importing all of the objects
Creating a new blank database, importing only the tables, and creating a
query
Creating a new database, creating new tables, and adding some test data

In all cases, the query would not allow data to be updated in any way. Here
is the basic structure of the test database. By the way, I tried using
unique names for the FK fields, but the result was the same as if I had used
VendorID in all cases.

tblVendor
VendorID (PK)
VendorName, etc.

tblPO
PO_ID (PK)
VendorID_PO (FK)
PurchaseDate, etc.

tblPhone
PhoneID (PK)
VendorID_phone (FK)
PhoneNumber

The relationships have referential integrity enforced, and appear to be as
they should. Changing the join type in the query makes no difference. The
query cannot be edited. I can only conclude this is designed behavior. I
just wish I knew why.

SELECT tblPO.*, tblVendor.*, tblPhone.Phone
FROM (tblVendor INNER JOIN tblPhone ON tblVendor.VendorID =
tblPhone.VendorID_phone) INNER JOIN tblPO ON tblVendor.VendorID =
tblPO.VendorID_PO;


Anyhow, thanks for taking a look at this. I have provided the information
in this posting just in case something jumps out at you, but this may have
to remain a mystery.

"Marshall Barton" wrote in message
...
I can't explain it. Barring something funny about your
query, it should work. How you created the table and the
relationships should have no effect as long as the query is
properly constructed, which it looks like it is.

Someone else may have an idea, but the only straw I can
think of to grasp at is some kind of corruption. Make a
backup copy of the mdb file, then try compacting the db,
recreating the query from scratch or even creating a fresh
mdb and importing everything.
--
Marsh
MVP [MS Access]


BruceM wrote:
Thanks for the reply. I guess I didn't make it clear that I was just
trying
to display, not edit, the vendor data. I'll have a command button or a
click event for the controls bound to the vendor data if the user needs to
go to the main vendor form, in which the phone numbers are displayed in a
list box. Phone numbers are added/edited by means of a pop-up form.
However, only a few users will be able to edit vendor data at all, so most
of the front ends will include just a message box to inform users they
can't
change the data.
A query that combines tblVendor and tblPhone can be edited, as can a query
that combines tblPO and tblVendor. FWIW, the only indexed fields are the
PK
and FK fields, and VendorName from tblVendor. It makes no difference if I
add fields individually or by using the asterisk to add all fields, nor if
I
remove the parameter. I tried breaking the relationship between tblPO and
tblPO_Details (which is the junction table between tblPO and tblProducts)
so
that a many-to-many relationship is completely out of the mix.
In order to get the phone numbers, which were in Phone1 and Phone2 fields
in
tblVendor, into their own table I based an append query on a union query.
I
wondered if this could have created some anomalies, so I create a new
tblPhone with no data. When I added tblPO, tblVendor, and the new
tblPhone
to a query it returned no records because tblPhone had none, so I changed
the join between tblVendor and tblPhone to a left join, after which I
could
see all of the PO records, but I still couldn't edit any fields. This
tells
me that when I prepare the report that is the actual printed PO I can
include all three tables in its record source, but need to use the left
join
in order to display a record regardless of whether there is a phone
number.
However, this doesn't get me any closer to displaying the phone number in
a
form based on an updatable query, unless I use a subform.
If a query that includes all three tables is supposed to be non-updatable
I
can accept that, but if I should be able to edit the PO fields but cannot
I
would like to discover why, and thereby learn something new.
I may end up using a subform to display the vendor information, including
phone, since it will be simpler to prevent edits or additions in the
subform
than in individual controls, but if you have any thoughts or explanations
about why I can't edit any of the fields when all three tables are in the
query, I would be interested in hearing about it.

"Marshall Barton" wrote.
BruceM wrote:
"Marshall Barton" wrote
BruceM wrote:

This is the SQL for a query that cannot be updated:

SELECT tblPO.*, tblVendor.*, tblPhone.Phone, tblPhone.MainPhone
FROM (tblVendor INNER JOIN tblPO ON tblVendor.VendorID =
tblPO.VendorID)
INNER JOIN tblPhone ON tblVendor.VendorID = tblPhone.VendorID
WHERE (((tblPhone.MainPhone)=True));

The situation, in general terms, is that I have a pretty standard PO
system.
There is a Vendor table, with a related Purchase Order (PO) table. If
it
matters for purposes of this question, there is a PO_Details table for
line
items on the PO, which serves as the junction table between the Vendor
table
and a Products table.
The Vendor table also has a related table for phone numbers. Therein
lies
my problem. The PO form needs to contain Vendor information including
the
phone number, and PO information such as PO_Date and PO_Number. As
long
as
I leave out the Phone table, everything is fine.
If I make a query consisting of tblVendor and either tblPhone or
tblPO,
everything is fine; however, adding both tblPhone and tblPO locks down
the
query for reasons I have not been able to discover. I do know that
changing
the join type has accomplished nothing. An article in Microsoft's
MSDN2
library contains the following as a reason for a non-updatable query:
"Query based on three or more tables in which there is a many-to-many
relationship"
This is the closest I can find to something that applies to my
situation,
as
tblPO is related to tblPO_Details, which as I mentioned is a junction
table.
The article is ambiguous in that it doesn't specify whether the
problem
lies
with a query that includes the three table involved in the
many-to-many,
or
just any query with three or more tables, one of which is part of a
many-to-many relationship. If it applies to my difficulties I do not
see
the solution.


Well, the first thing is that you should not be treating a
multi-table dataset as a data editing mechanism. I think(?)
many other db systems just plain disallow it. Access/Jet
will sometimes permit it in an effort (unjustified in my
opinion) to make things easier for people that don't know
any better.

I never intended to edit the phone number from the PO form, only to
display
it. The display-only fields were to be in locked controls. Do I
understand
you to mean a forms's record source should be either a table or a
single-table query? Would it be better to have a subform for the vendor
information such as address (and phone) that is displayed only? The
editable fields could be on the main (PO) form, and the displayed-only
fields on the subform, which could be locked to prevent edits. I have
to
say I always thought multi-table Record Source queries were a strength
of
Access/Jet, not a liability.

The only hope you have of making this
arrangement work is to include each table's primary key in
the dataset.

Tried that. It didn't work.

The "proper" way to deal with updating related information
in multiple tables is to use a form for the table of primary
interest and a separate subform for each related table's
data.

I just wish there was a convenient way of handling one-field subforms so
that they look like text boxes. There's lots of trial and error, and in
the
end a control that doesn't quite look like the rest of the controls.

Anyhow, the subform solves the immediate problem, of course, even if it
is
a
fiddly nuisance to work with. I will admit, though, to being moderately
confused about datasets. Thanks for taking the time to look at my
questions, as you have to very good effect on a number of past
occasions.


I thought you were trying to modify the phone field. Other
than that issue, I think I just added to your confusion.
I have more form record source queries than I can count that
use multiple tables and as long as only the primary table's
fields are being updated, there should be no problem. The
problem I was trying to address is in editing a many side
table's fields.

Just guessing now, but maybe something else going on. Can
you find anything unusual about the phones table? Try
joining that table just to the vendor table and see if the
vendor data can be modified.

BruceM wrote:
I have tried:
Creating a new query
Compacting and repairing the database
Creating a new blank database, and importing all of the objects
Creating a new blank database, importing only the tables, and
creating a query
Creating a new database, creating new tables, and adding some test
data
In all cases, the query would not allow data to be updated in any
way. Here is the basic structure of the test database. By the way,
I tried using unique names for the FK fields, but the result was the
same as if I had used VendorID in all cases.

tblVendor
VendorID (PK)
VendorName, etc.

tblPO
PO_ID (PK)
VendorID_PO (FK)
PurchaseDate, etc.

tblPhone
PhoneID (PK)
VendorID_phone (FK)
PhoneNumber

The relationships have referential integrity enforced, and appear to
be as they should. Changing the join type in the query makes no
difference. The query cannot be edited. I can only conclude this is
designed behavior. I just wish I knew why.

SELECT tblPO.*, tblVendor.*, tblPhone.Phone
FROM (tblVendor INNER JOIN tblPhone ON tblVendor.VendorID =
tblPhone.VendorID_phone) INNER JOIN tblPO ON tblVendor.VendorID =
tblPO.VendorID_PO;


Anyhow, thanks for taking a look at this. I have provided the
information in this posting just in case something jumps out at you,
but this may have to remain a mystery.

I don't know that the "rules" for when query is updateable are really that
well-defined for the case of queries that include multiple tables. It might
very well be influenced by the query plan that is compiled and saved for the
query which can varies based on indexes available and by the actual data in
the tables.

It should be pointed out that editable record sets from ANY query having
multiple table inputs is not allowed in most databases. Access is one of
the most flexible in this regard, but (as observed) there are limits to how
complex a query's joins can be before *safely* allowing edits can be
executed and as a safeguard the result set becomes read only when any
ambiguity creeps in.

In all my years of using Access I have only a small handful of cases where I
have attempted to edit data from a multi-input query. It is just not that
necessary of a thing to do.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Thanks for the reply. I am not attempting to add or edit data other than
Purchase Order information. Further, I have no wish to do so. VendorID is
the FK field in tblPO, so the combo box on frmPO has been made to display
the vendor name, but the rest of the vendor information is for display only.
Since there were only to be three such fields (a concatenated address field,
fax, and phone) I thought it would be simple enough to just lock those three
text boxes and not bother with a subform, but it is not possible. I believe
the limitation in my case is that two tables were linked to the same field
in the third table. Both tblPhone and tblPO have FK fields related to the
same PK field (VendorID) from tblVendor. The query I described is not
updatable. However, a query combining tblVendor, tblPO, and tblPO_Details
is editable. Each vendor may have many POs, and each PO may have many line
items. This is a different situation than I have described, in which each
vendor may have many POs, and each vendor may have many phone numbers.
Once again, I do not intend, in this project or any other, to edit fields
from more than one table that is included in a query, unless some
exceptional circumstances present themselves in the future. There are times
when it would be convenient if I did not have to use a subform, particularly
when I only need to display a single field, but now that I have a better
handle on the limitations of multi-table queries as form record sources I
can move on to other things. In the present case I constructed a locked
subform that combines tblVendor and tblPhone.

"Rick Brandt" wrote in message
t...
BruceM wrote:
I have tried:
Creating a new query
Compacting and repairing the database
Creating a new blank database, and importing all of the objects
Creating a new blank database, importing only the tables, and
creating a query
Creating a new database, creating new tables, and adding some test
data
In all cases, the query would not allow data to be updated in any
way. Here is the basic structure of the test database. By the way,
I tried using unique names for the FK fields, but the result was the
same as if I had used VendorID in all cases.

tblVendor
VendorID (PK)
VendorName, etc.

tblPO
PO_ID (PK)
VendorID_PO (FK)
PurchaseDate, etc.

tblPhone
PhoneID (PK)
VendorID_phone (FK)
PhoneNumber

The relationships have referential integrity enforced, and appear to
be as they should. Changing the join type in the query makes no
difference. The query cannot be edited. I can only conclude this is
designed behavior. I just wish I knew why.

SELECT tblPO.*, tblVendor.*, tblPhone.Phone
FROM (tblVendor INNER JOIN tblPhone ON tblVendor.VendorID =
tblPhone.VendorID_phone) INNER JOIN tblPO ON tblVendor.VendorID =
tblPO.VendorID_PO;


Anyhow, thanks for taking a look at this. I have provided the
information in this posting just in case something jumps out at you,
but this may have to remain a mystery.

I don't know that the "rules" for when query is updateable are really
that well-defined for the case of queries that include multiple tables.
It might very well be influenced by the query plan that is compiled and
saved for the query which can varies based on indexes available and by the
actual data in the tables.

It should be pointed out that editable record sets from ANY query having
multiple table inputs is not allowed in most databases. Access is one of
the most flexible in this regard, but (as observed) there are limits to
how complex a query's joins can be before *safely* allowing edits can be
executed and as a safeguard the result set becomes read only when any
ambiguity creeps in.

In all my years of using Access I have only a small handful of cases where
I have attempted to edit data from a multi-input query. It is just not
that necessary of a thing to do.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

BruceM wrote:
Thanks for the reply. I am not attempting to add or edit data other
than Purchase Order information. Further, I have no wish to do so.
VendorID is the FK field in tblPO, so the combo box on frmPO has been
made to display the vendor name, but the rest of the vendor
information is for display only. [snip]

I have had queries where linking to one additional table so that one field
could be added for viewing only made that query non-editable. Which fields
you intend to edit or not edit doesn't enter into the equation.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

BruceM wrote:

I have tried:
Creating a new query
Compacting and repairing the database
Creating a new blank database, and importing all of the objects
Creating a new blank database, importing only the tables, and creating a
query
Creating a new database, creating new tables, and adding some test data

In all cases, the query would not allow data to be updated in any way. Here
is the basic structure of the test database. By the way, I tried using
unique names for the FK fields, but the result was the same as if I had used
VendorID in all cases.

tblVendor
VendorID (PK)
VendorName, etc.

tblPO
PO_ID (PK)
VendorID_PO (FK)
PurchaseDate, etc.

tblPhone
PhoneID (PK)
VendorID_phone (FK)
PhoneNumber

The relationships have referential integrity enforced, and appear to be as
they should. Changing the join type in the query makes no difference. The
query cannot be edited. I can only conclude this is designed behavior. I
just wish I knew why.

SELECT tblPO.*, tblVendor.*, tblPhone.Phone
FROM (tblVendor INNER JOIN tblPhone ON tblVendor.VendorID =
tblPhone.VendorID_phone) INNER JOIN tblPO ON tblVendor.VendorID =
tblPO.VendorID_PO;


Anyhow, thanks for taking a look at this. I have provided the information
in this posting just in case something jumps out at you, but this may have
to remain a mystery.


Ok, I just spent the last hour duplicating your arrangement
and experiments and I am seeing the same things you are.
All the playing around with it that I've done did not help
any more than what you've been saying all along.

I also went into several of my apps looking for examples
where I've done similar things and, regardless of my
(apparently failing) memory, I can't find any examples where
I actually try to use a three table query for updating
anything. I guess I was off base before when I said I had
done this before.

I did find lots of places where I used a dependent combo
box, a subform or, ocassionally a text box with a DLookup to
display data from a third table.

--
Marsh
MVP [MS Access]

Yup, I see that it's all or nothing with updatable-ness.

"Rick Brandt" wrote in message
et...
BruceM wrote:
Thanks for the reply. I am not attempting to add or edit data other
than Purchase Order information. Further, I have no wish to do so.
VendorID is the FK field in tblPO, so the combo box on frmPO has been
made to display the vendor name, but the rest of the vendor
information is for display only. [snip]

I have had queries where linking to one additional table so that one field
could be added for viewing only made that query non-editable. Which
fields you intend to edit or not edit doesn't enter into the equation.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Ah! A text box or something with a DLookup, as I did with the list box for
displaying phone numbers (from the related table) in my Vendors form. I
didn't make the connection that I could probably have done the same sort of
thing. I use lots of subforms, but they can be awkward to work with in that
I can't see how they line up until I switch to Form view. If it's not right
I switch back, tweak, and repeat. As I understand this has been improved in
Access 2007, but I won't be seeing that anytime soon, at least not at work.
Anyhow, as I think I mentioned I ended up using a subform for vendor
information, which did have the advantage of allowing me to disallow changes
in one shot at the form level.
As I mentioned to Rick, I realized that in the situation where each vendor
may have many Purchase Orders, and each PO may have many line items, the
query is updatable. However, in my case each vendor could have many POs,
and each vendor could have many phone numbers. Both tblPO and tblPhone were
trying to "share" the same PK from tblVendor. I don't quite understand the
rule that applies, but I see how it works.
Marshall, thanks for taking the time to look into this. Even after I
decided the subform as described was a good way to go about things, I
stubbornly continued in my efforts to discover what was going on. Perhaps
it wasn't the best use of my time in the short term, but I expect the
hard-earned information I gleaned will stay with me pretty well. Thanks for
sticking with me through the investigation.

"Marshall Barton" wrote in message
...
BruceM wrote:

I have tried:
Creating a new query
Compacting and repairing the database
Creating a new blank database, and importing all of the objects
Creating a new blank database, importing only the tables, and creating a
query
Creating a new database, creating new tables, and adding some test data

In all cases, the query would not allow data to be updated in any way.
Here
is the basic structure of the test database. By the way, I tried using
unique names for the FK fields, but the result was the same as if I had
used
VendorID in all cases.

tblVendor
VendorID (PK)
VendorName, etc.

tblPO
PO_ID (PK)
VendorID_PO (FK)
PurchaseDate, etc.

tblPhone
PhoneID (PK)
VendorID_phone (FK)
PhoneNumber

The relationships have referential integrity enforced, and appear to be as
they should. Changing the join type in the query makes no difference.
The
query cannot be edited. I can only conclude this is designed behavior. I
just wish I knew why.

SELECT tblPO.*, tblVendor.*, tblPhone.Phone
FROM (tblVendor INNER JOIN tblPhone ON tblVendor.VendorID =
tblPhone.VendorID_phone) INNER JOIN tblPO ON tblVendor.VendorID =
tblPO.VendorID_PO;


Anyhow, thanks for taking a look at this. I have provided the information
in this posting just in case something jumps out at you, but this may have
to remain a mystery.


Ok, I just spent the last hour duplicating your arrangement
and experiments and I am seeing the same things you are.
All the playing around with it that I've done did not help
any more than what you've been saying all along.

I also went into several of my apps looking for examples
where I've done similar things and, regardless of my
(apparently failing) memory, I can't find any examples where
I actually try to use a three table query for updating
anything. I guess I was off base before when I said I had
done this before.

I did find lots of places where I used a dependent combo
box, a subform or, ocassionally a text box with a DLookup to
display data from a third table.

--
Marsh
MVP [MS Access]

BruceM wrote:

Ah! A text box or something with a DLookup, as I did with the list box for
displaying phone numbers (from the related table) in my Vendors form. I
didn't make the connection that I could probably have done the same sort of
thing. I use lots of subforms, but they can be awkward to work with in that
I can't see how they line up until I switch to Form view. If it's not right
I switch back, tweak, and repeat. As I understand this has been improved in
Access 2007, but I won't be seeing that anytime soon, at least not at work.
Anyhow, as I think I mentioned I ended up using a subform for vendor
information, which did have the advantage of allowing me to disallow changes
in one shot at the form level.
As I mentioned to Rick, I realized that in the situation where each vendor
may have many Purchase Orders, and each PO may have many line items, the
query is updatable. However, in my case each vendor could have many POs,
and each vendor could have many phone numbers. Both tblPO and tblPhone were
trying to "share" the same PK from tblVendor. I don't quite understand the
rule that applies, but I see how it works.
Marshall, thanks for taking the time to look into this. Even after I
decided the subform as described was a good way to go about things, I
stubbornly continued in my efforts to discover what was going on. Perhaps
it wasn't the best use of my time in the short term, but I expect the
hard-earned information I gleaned will stay with me pretty well. Thanks for
sticking with me through the investigation.


You're welcome, even though I was at least part of the
inefficient use of your time.

--
Marsh
MVP [MS Access]

I learned something from all of this. It more than makes up for the brief
time I spent heading down the wrong road. Also, you confirmed that the
problem was not some undiscovered fault in my technique. The problem was
that my query could not work, not that I was constructing it incorrectly.
You could have just shrugged and walked away, but you took the time to
investigate, which I appreciate.

"Marshall Barton" wrote in message
...
BruceM wrote:

Ah! A text box or something with a DLookup, as I did with the list box
for
displaying phone numbers (from the related table) in my Vendors form. I
didn't make the connection that I could probably have done the same sort
of
thing. I use lots of subforms, but they can be awkward to work with in
that
I can't see how they line up until I switch to Form view. If it's not
right
I switch back, tweak, and repeat. As I understand this has been improved
in
Access 2007, but I won't be seeing that anytime soon, at least not at
work.
Anyhow, as I think I mentioned I ended up using a subform for vendor
information, which did have the advantage of allowing me to disallow
changes
in one shot at the form level.
As I mentioned to Rick, I realized that in the situation where each vendor
may have many Purchase Orders, and each PO may have many line items, the
query is updatable. However, in my case each vendor could have many POs,
and each vendor could have many phone numbers. Both tblPO and tblPhone
were
trying to "share" the same PK from tblVendor. I don't quite understand
the
rule that applies, but I see how it works.
Marshall, thanks for taking the time to look into this. Even after I
decided the subform as described was a good way to go about things, I
stubbornly continued in my efforts to discover what was going on. Perhaps
it wasn't the best use of my time in the short term, but I expect the
hard-earned information I gleaned will stay with me pretty well. Thanks
for
sticking with me through the investigation.


You're welcome, even though I was at least part of the
inefficient use of your time.

--
Marsh
MVP [MS Access]