fhwa said:
Hello, I'm having the same problem and I cannot figure out the
problem. Here is my SQL and the design view is pulling information
from 5 different tables that are all related and have primary/foreign
keys. I appreciate the help I need to be able to edit the information
with this query along with a form that is made from it.
SELECT tblStrategicGoals2.StrategicGoal,
tblStategicOutcomePlan.NationalPerformanceGoal2007 AS Expr1,
tblStategicOutcomePlan.NationalPerformanceGoal2008,
tblStategicOutcomePlan.DivisionOutcome2007,
tblStategicOutcomePlan.DivisionOutcome2008,
tblMeasuresAndTargets.Measure2006 AS [Measure 2007],
tblMeasuresAndTargets.Measure2007 AS [Measure 2008],
tblMeasuresAndTargets.Target2006 AS [Target 2007],
tblMeasuresAndTargets.Target2007 AS [Target 2008],
tblEmployeeSOPID.SOPID, tblEmployeeSOPID.EmployeeID,
tblStategicOutcomePlan.OutcomeChampion, tblStategicOutcomePlan.Focus,
tblEmployees.LoginName
FROM tblStrategicGoals2 INNER JOIN ((tblStategicOutcomePlan INNER JOIN
(tblEmployees INNER JOIN tblEmployeeSOPID ON tblEmployees.EmployeeID =
tblEmployeeSOPID.EmployeeID) ON tblStategicOutcomePlan.SOPID =
tblEmployeeSOPID.SOPID) INNER JOIN tblMeasuresAndTargets ON
tblStategicOutcomePlan.SOPID = tblMeasuresAndTargets.sopID) ON
tblStrategicGoals2.StrategicGoalID =
tblStategicOutcomePlan.StrategicGoalID
WHERE (((tblEmployeeSOPID.SOPID)>60))
GROUP BY tblStrategicGoals2.StrategicGoal,
tblStategicOutcomePlan.NationalPerformanceGoal2007,
tblStategicOutcomePlan.NationalPerformanceGoal2008,
tblStategicOutcomePlan.DivisionOutcome2007,
tblStategicOutcomePlan.DivisionOutcome2008,
tblMeasuresAndTargets.Measure2006, tblMeasuresAndTargets.Measure2007,
tblMeasuresAndTargets.Target2006, tblMeasuresAndTargets.Target2007,
tblEmployeeSOPID.SOPID, tblEmployeeSOPID.EmployeeID,
tblStategicOutcomePlan.OutcomeChampion, tblStategicOutcomePlan.Focus,
tblEmployees.LoginName
HAVING (((tblEmployees.LoginName)="dbullock"))
ORDER BY tblStrategicGoals2.StrategicGoal,
tblStategicOutcomePlan.NationalPerformanceGoal2007,
tblStategicOutcomePlan.DivisionOutcome2007;
fhwa,
Your query above has a GROUP BY.
Please read onward.
--------------------------------------
Updatability Restrictions for Multiple-Table Queries
To be fully updatable, a query must meet several requirements:
You must specify an explicit inner or outer join between tables. Joins created implicitly
in the WHERE clause of the SELECT statement aren't updatable. For example, the following
join isn't updatable:
SELECT
Products.ProductID,
Products.ProductName,
Categories.CategoryID,
Categories.CategoryName
FROM Categories, Products
WHERE Products.CategoryID = Categories.CategoryID;
Summary (GROUP BY), UNION, DISTINCT, and crosstab queries are never updatable. Queries
joined to one or more summary queries aren't updatable, even if you don't attempt to
modify fields from an otherwise updatable table. However, a query may be updatable if it
refers to a summary query in a sub-SELECT statement, as in the following example:
SELECT Orders.*
FROM Orders
WHERE Orders.Freight >
(SELECT Avg(Orders.Freight) AS AvgOfFreight
FROM Orders
;
In this case, fields from the Orders table are updatable.
To be able to insert new records into a table in any query, all primary key fields must be
present.
While you're updating a single record in a query, changes to certain fields may render
certain other fields nonupdatable until the edit to the record is either saved or
canceled. As soon as the user edits data on the "one" side of a query, the join key on the
"many" side can no longer be modified. Usually, the "many" side's join key is updatable.
However, because data on the "one" side was modified first, this field is temporarily
rendered unmodifiable because row fix-up would discard changes to the "one" side's data.
As soon as the change to the "one" side of the query is saved or canceled, the "many"
side's join key becomes updatable again.
A change to a multiple-table query must not create orphaned records. You can change the
join key in the "many" table to a value already present in the "one" table, but you can't
specify a nonexistent value, except in the case of outer joins.
--------------------------------------
Sincerely,
Chris O.
