followup - what makes this query not updatable?

[lynn atkinson]

This is a continuation from last weeks posting regarding this question.
I managed to solve some of the problems in that I had a table appearing
twice in the relationships window, therefore I was getting a duplicated
statement. However I still cannot work out why the following recordset is not
updatable.

SELECT bookings.[event ID], bookings.[booking ID], bookings.employeeID,
employeedetails.surname, employeedetails.forename, employeedetails.[post ID],
[post details].[post ID], [post details].[post/role], [post
details].location, [Project Codes].Project, bookings.[booking taken],
bookings.[confirmed date], bookings.confirmed, bookings.status,
bookings.explain, employeedetails.[support needs], bookings.[cert issued],
[post details].[project code], bookings.datemodified, bookings.datecreated
FROM ([post details] LEFT JOIN [Project Codes] ON [post details].[project
code] = [Project Codes].Code) INNER JOIN ((employeedetails INNER JOIN
bookings ON employeedetails.[employee ID new] = bookings.employeeID) INNER
JOIN contractual ON employeedetails.[employee ID new] = contractual.[employee
ID]) ON [post details].[post ID] = contractual.[post ID]
ORDER BY bookings.[booking taken];

Can anyone explain why I cannot update this query? Is there something I can
do to make this updatable?

regards

 

[Rick Brandt]

This is a continuation from last weeks posting regarding this
question.
I managed to solve some of the problems in that I had a table
appearing twice in the relationships window, therefore I was getting
a duplicated statement. However I still cannot work out why the
following recordset is not updatable.

SELECT bookings.[event ID], bookings.[booking ID],
bookings.employeeID, employeedetails.surname,
employeedetails.forename, employeedetails.[post ID], [post
details].[post ID], [post details].[post/role], [post
details].location, [Project Codes].Project, bookings.[booking taken],
bookings.[confirmed date], bookings.confirmed, bookings.status,
bookings.explain, employeedetails.[support needs], bookings.[cert
issued], [post details].[project code], bookings.datemodified,
bookings.datecreated FROM ([post details] LEFT JOIN [Project Codes]
ON [post details].[project code] = [Project Codes].Code) INNER JOIN
((employeedetails INNER JOIN bookings ON employeedetails.[employee ID
new] = bookings.employeeID) INNER JOIN contractual ON
employeedetails.[employee ID new] = contractual.[employee ID]) ON
[post details].[post ID] = contractual.[post ID]
ORDER BY bookings.[booking taken];

Can anyone explain why I cannot update this query? Is there something
I can do to make this updatable?

There is an entire help topic that discusses this. Look for "When can I update
data in a query?". For any given situation it can be a complicated issue to
dicipher and resolve (it is not always resolvable).

In a nutshell, the more tables and queries that are used as inputs to a SELECT
query the more difficult it is for the database engine to determine where
updates should go (which tables they should be applied to). Once a certain
ambiguity threshold is crossed the query is rendered as read-only rather than
risk corrupting the data.

As I often point out, Access/Jet is more forgiving in allowing updates in
multi-input queries than just about any other engine. In many database engines
there is no such thing (at all) as a query/view joining more than one source
that still allows edits.

 

[lynn atkinson]

OK I get what you are saying, but in trying to understand what is going on, I
have stripped the query right back to contain only 3 tables, which again are
in combination not updatable. These tables are the main data sources and are
contracual, bookings and employeedetails. Could it be something to do with
the join types?

This is a continuation from last weeks posting regarding this
question.
I managed to solve some of the problems in that I had a table
appearing twice in the relationships window, therefore I was getting
a duplicated statement. However I still cannot work out why the
following recordset is not updatable.

SELECT bookings.[event ID], bookings.[booking ID],
bookings.employeeID, employeedetails.surname,
employeedetails.forename, employeedetails.[post ID], [post
details].[post ID], [post details].[post/role], [post
details].location, [Project Codes].Project, bookings.[booking taken],
bookings.[confirmed date], bookings.confirmed, bookings.status,
bookings.explain, employeedetails.[support needs], bookings.[cert
issued], [post details].[project code], bookings.datemodified,
bookings.datecreated FROM ([post details] LEFT JOIN [Project Codes]
ON [post details].[project code] = [Project Codes].Code) INNER JOIN
((employeedetails INNER JOIN bookings ON employeedetails.[employee ID
new] = bookings.employeeID) INNER JOIN contractual ON
employeedetails.[employee ID new] = contractual.[employee ID]) ON
[post details].[post ID] = contractual.[post ID]
ORDER BY bookings.[booking taken];

Can anyone explain why I cannot update this query? Is there something
I can do to make this updatable?

There is an entire help topic that discusses this. Look for "When can I update
data in a query?". For any given situation it can be a complicated issue to
dicipher and resolve (it is not always resolvable).

In a nutshell, the more tables and queries that are used as inputs to a SELECT
query the more difficult it is for the database engine to determine where
updates should go (which tables they should be applied to). Once a certain
ambiguity threshold is crossed the query is rendered as read-only rather than
risk corrupting the data.

As I often point out, Access/Jet is more forgiving in allowing updates in
multi-input queries than just about any other engine. In many database engines
there is no such thing (at all) as a query/view joining more than one source
that still allows edits.

 

[lynn atkinson]

thanks for this. Have looked up the help as suggested and have found that my
query has many-to-one-to-many relationship. I have therefore set the form's
RecordsetType property is set to Dynaset (Inconsistent Updates) and this has
solved my problem.

Thanks for your help

This is a continuation from last weeks posting regarding this
question.
I managed to solve some of the problems in that I had a table
appearing twice in the relationships window, therefore I was getting
a duplicated statement. However I still cannot work out why the
following recordset is not updatable.

SELECT bookings.[event ID], bookings.[booking ID],
bookings.employeeID, employeedetails.surname,
employeedetails.forename, employeedetails.[post ID], [post
details].[post ID], [post details].[post/role], [post
details].location, [Project Codes].Project, bookings.[booking taken],
bookings.[confirmed date], bookings.confirmed, bookings.status,
bookings.explain, employeedetails.[support needs], bookings.[cert
issued], [post details].[project code], bookings.datemodified,
bookings.datecreated FROM ([post details] LEFT JOIN [Project Codes]
ON [post details].[project code] = [Project Codes].Code) INNER JOIN
((employeedetails INNER JOIN bookings ON employeedetails.[employee ID
new] = bookings.employeeID) INNER JOIN contractual ON
employeedetails.[employee ID new] = contractual.[employee ID]) ON
[post details].[post ID] = contractual.[post ID]
ORDER BY bookings.[booking taken];

Can anyone explain why I cannot update this query? Is there something
I can do to make this updatable?

There is an entire help topic that discusses this. Look for "When can I update
data in a query?". For any given situation it can be a complicated issue to
dicipher and resolve (it is not always resolvable).

In a nutshell, the more tables and queries that are used as inputs to a SELECT
query the more difficult it is for the database engine to determine where
updates should go (which tables they should be applied to). Once a certain
ambiguity threshold is crossed the query is rendered as read-only rather than
risk corrupting the data.

As I often point out, Access/Jet is more forgiving in allowing updates in
multi-input queries than just about any other engine. In many database engines
there is no such thing (at all) as a query/view joining more than one source
that still allows edits.