K
Kayda
Hi:
I am trying to update several fields in one table based on a join. The
query below doesn't work, but it should be obvious what I am trying to
do:
UPDATE AgentStats INNER JOIN
(SELECT dt_DateField, AgentLogin,
SUM(IIF ([ActivityCodeName] = "Attending Training", [Occurrences], 0))
AS AttendingTraining,
SUM(IIF ([ActivityCodeName] = "Break", [Occurrences], 0)) AS Break
FROM dActivityCodeStat GROUP BY Timestamp, AgentLogin
) b ON (AgentStats.AgentDate = b.dt_DateField) AND
(AgentStats.AgentLogin = b.AgentLogin) SET
[AgentStats].[Mandated] = b.[AttendingTraining], [AgentStats].[Non-
Mandated] = b.[Break];
So basically I want to link the AgentStats table to what is in the
parenthesis (aliased as "b") on the Login and the date. What is the
correct syntax in Access? When I try to run the above I get an
"operation must use an updateable query". I am executing this query
from Access itself, not from outside. There are no constraints between
the two tables. The inner query ("b") works fine on its own.
Thanks,
Kayda
I am trying to update several fields in one table based on a join. The
query below doesn't work, but it should be obvious what I am trying to
do:
UPDATE AgentStats INNER JOIN
(SELECT dt_DateField, AgentLogin,
SUM(IIF ([ActivityCodeName] = "Attending Training", [Occurrences], 0))
AS AttendingTraining,
SUM(IIF ([ActivityCodeName] = "Break", [Occurrences], 0)) AS Break
FROM dActivityCodeStat GROUP BY Timestamp, AgentLogin
) b ON (AgentStats.AgentDate = b.dt_DateField) AND
(AgentStats.AgentLogin = b.AgentLogin) SET
[AgentStats].[Mandated] = b.[AttendingTraining], [AgentStats].[Non-
Mandated] = b.[Break];
So basically I want to link the AgentStats table to what is in the
parenthesis (aliased as "b") on the Login and the date. What is the
correct syntax in Access? When I try to run the above I get an
"operation must use an updateable query". I am executing this query
from Access itself, not from outside. There are no constraints between
the two tables. The inner query ("b") works fine on its own.
Thanks,
Kayda