what makes this query not updatable

[lynn atkinson]

I have a simple query connecting employee details to their contract
information. Why is this not an updatable query? It is a one to many
relationship - an employee can have many contracts.
Can anyone explain simply to me why this cannot be updated? What can I not
see? Is it a design fault?
The sql of this example is
SELECT employeedetails.surname, contractual.[contract type]
FROM employeedetails INNER JOIN contractual ON (employeedetails.[employee ID
new] = contractual.[employee ID]) AND (employeedetails.[employee ID new] =
contractual.[employee ID]);

This is quite fundimental to my database.
Cheers

 

[lynn atkinson]

here is another statement which doesnt work - why?
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]) AND (employeedetails.[employee ID new] =
contractual.[employee ID])) ON [post details].[post ID] = contractual.[post
ID]
ORDER BY bookings.[booking taken];

the query worked before I added the contractual table to the query. I dont
get this!

 

[Allen Browne]

Lynn, why is the "ON" duplicated in the JOIN?

 

[lynn atkinson]

dont know. each time I create the query, this appears twice. If I take one of
the duplicates out of the simple query, it works, however, I posted a more
complicated query which isnt updatable even if I take the duplicated bit out.

Where should I look for the source of this duplication? Is it in the
relationships or design?

Lynn, why is the "ON" duplicated in the JOIN?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a simple query connecting employee details to their contract
information. Why is this not an updatable query? It is a one to many
relationship - an employee can have many contracts.
Can anyone explain simply to me why this cannot be updated? What can I not
see? Is it a design fault?
The sql of this example is
SELECT employeedetails.surname, contractual.[contract type]
FROM employeedetails INNER JOIN contractual ON (employeedetails.[employee
ID
new] = contractual.[employee ID]) AND (employeedetails.[employee ID new] =
contractual.[employee ID]);

This is quite fundimental to my database.
Cheers

 

[lynn atkinson]

found the cause of this. Somehow I had the employee details table twice in
the relationships. I have now deleted this but I am still not understanding
why the more complex queries with employee details and contracts tables etc
are not updateable

Lynn, why is the "ON" duplicated in the JOIN?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a simple query connecting employee details to their contract
information. Why is this not an updatable query? It is a one to many
relationship - an employee can have many contracts.
Can anyone explain simply to me why this cannot be updated? What can I not
see? Is it a design fault?
The sql of this example is
SELECT employeedetails.surname, contractual.[contract type]
FROM employeedetails INNER JOIN contractual ON (employeedetails.[employee
ID
new] = contractual.[employee ID]) AND (employeedetails.[employee ID new] =
contractual.[employee ID]);

This is quite fundimental to my database.
Cheers

 

[Allen Browne]

Lynn, make sure the Name AutoCorrect boxes are unchecked under:
Tools | Options | General
Then compact the database.

If the bad relationships return, delete these relations, and compact again.
You should then be able to recreate them, and have them behave properly.

Once that's sorted out, you may find that the query settles down and behaves
as expected, i.e. the problems are actually the result of an incipient
corruption.

Most of the common reasons for read-only queries don't apply in your case,
but here's my standard list anyway:

.. It has a GROUP BY clause (totals query).
.. It has a TRANSFORM clause (crosstab query).
.. It contains a DISTINCT predicate.
.. It uses First(), Sum(), Max(), Count(), etc. in the SELECT clause
(performs aggregation).
.. It involves a UNION.
.. It has a subquery in the SELECT clause.
.. It uses JOINs of different directions on multiple tables in the FROM
clause.
.. The query is based on another query that is read-only (stacked query.)
.. Your permissions are read-only (Access security.)
.. The database is opened read-only, or the file attributes are read-only, or
the database is on read-only media (e.g. CD-ROM, network drive without write
privileges.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

found the cause of this. Somehow I had the employee details table twice in
the relationships. I have now deleted this but I am still not
understanding
why the more complex queries with employee details and contracts tables
etc
are not updateable

Lynn, why is the "ON" duplicated in the JOIN?

I have a simple query connecting employee details to their contract
information. Why is this not an updatable query? It is a one to many
relationship - an employee can have many contracts.
Can anyone explain simply to me why this cannot be updated? What can I
not
see? Is it a design fault?
The sql of this example is
SELECT employeedetails.surname, contractual.[contract type]
FROM employeedetails INNER JOIN contractual ON
(employeedetails.[employee
ID
new] = contractual.[employee ID]) AND (employeedetails.[employee ID
new] =
contractual.[employee ID]);

This is quite fundimental to my database.
Cheers

 

[JLamb]

Almost any time you do an update on currently joined table particularly when
you are using other table criteria to restrict your dataset you should make
sure that the Unique Rows query property is set to 'Yes'. That almost always
fixes the "not updateable" error.

 

[lynn atkinson]

Not sure where you are coming from here. Where do I find the unique rows
query property? Is that from the form?

Almost any time you do an update on currently joined table particularly when
you are using other table criteria to restrict your dataset you should make
sure that the Unique Rows query property is set to 'Yes'. That almost always
fixes the "not updateable" error.

I have a simple query connecting employee details to their contract
information. Why is this not an updatable query? It is a one to many
relationship - an employee can have many contracts.
Can anyone explain simply to me why this cannot be updated? What can I not
see? Is it a design fault?
The sql of this example is
SELECT employeedetails.surname, contractual.[contract type]
FROM employeedetails INNER JOIN contractual ON (employeedetails.[employee ID
new] = contractual.[employee ID]) AND (employeedetails.[employee ID new] =
contractual.[employee ID]);

This is quite fundimental to my database.
Cheers