why can I not edit a query

  • Thread starter Thread starter lynn
  • Start date Start date
L

lynn

I have a query containing 2 tables - salary and
contractual. I cannot edit anything in this query. If I
remove the salary table, the query becomes editable.
Why can I not edit a query containing the salary table?
Where do I check for 'editable' properties of the salary
table?
 
Hi,


Posting the SQL text of the query may help.

A query with any of the following is not updateable (just the most
frequent causes):

- An aggregate in the SELECT clause (which includes, for sure,
crosstab). An aggregate is any among the following: COUNT, SUM, MIN, MAX,
LAST, FIRST, AVG, SDEV.
- A non updateable query/virtual table in the FROM clause
- A UNION query
- An implicit inner join between tables in the WHERE clause rather
than an explicit INNER JOIN in the FROM clause. As example, "Product By
Category", in Northwind, is updateable,


SELECT Categories.CategoryName, Products.ProductName,
Products.QuantityPerUnit, Products.UnitsInStock, Products.Discontinued
FROM Categories INNER JOIN Products ON Categories.CategoryID =
Products.CategoryID
WHERE (Products.Discontinued<>Yes)
ORDER BY Categories.CategoryName, Products.ProductName;

but change it to its equivalent:

SELECT Categories.CategoryName, Products.ProductName,
Products.QuantityPerUnit, Products.UnitsInStock, Products.Discontinued
FROM Categories , Products
WHERE (Categories.CategoryID = Products.CategoryID) AND
(Products.Discontinued<>Yes)
ORDER BY Categories.CategoryName, Products.ProductName;

and it turns not-updateable.



Hoping it may help,
Vanderghast, Access MVP
 
-----Original Message-----
Hi,


Posting the SQL text of the query may help.

A query with any of the following is not updateable (just the most
frequent causes):

- An aggregate in the SELECT clause (which includes, for sure,
crosstab). An aggregate is any among the following: COUNT, SUM, MIN, MAX,
LAST, FIRST, AVG, SDEV.
- A non updateable query/virtual table in the FROM clause
- A UNION query
- An implicit inner join between tables in the WHERE clause rather
than an explicit INNER JOIN in the FROM clause. As example, "Product By
Category", in Northwind, is updateable,


SELECT Categories.CategoryName, Products.ProductName,
Products.QuantityPerUnit, Products.UnitsInStock, Products.Discontinued
FROM Categories INNER JOIN Products ON Categories.CategoryID =
Products.CategoryID
WHERE (Products.Discontinued<>Yes)
ORDER BY Categories.CategoryName, Products.ProductName;

but change it to its equivalent:

SELECT Categories.CategoryName, Products.ProductName,
Products.QuantityPerUnit, Products.UnitsInStock, Products.Discontinued
FROM Categories , Products
WHERE (Categories.CategoryID = Products.CategoryID) AND
(Products.Discontinued<>Yes)
ORDER BY Categories.CategoryName, Products.ProductName;

and it turns not-updateable.



Hoping it may help,
Vanderghast, Access MVP





.
Click to expand...
Thanks for your reply. I have discovered the problem was
due to the relationship being 'indeterminate'. I changed
one of the join fields to be indexed (no duplicates) and
the relationship became one to many which is what I was
expecting. The query and form based on the query now works.
 
Back
Top