Joined UPDATE query

[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

 

[Guest]

Hi

The syntax of your query is essentially correct (although you do have
Timestamp in the GROUP BY instead of dt_DateField).

However, you can not update using a summary query like this.

Have a look at the Access help titled "When can i update from a query?"

To make your update query work you need to use domain aggregate functions
such as DSUM.

The following should work for you...

UPDATE AgentStats
SET AgentStats.Mandated = nz(DSum("Occurrences", "dActivityCodeStat",
"ActivityCodeName = 'Attending Training' and AgentLogin = '" & [AgentLogin]
& "' and dt_Datefield = #" & Format([AgentDate],'mm/dd/yyyy') & "#"),0),
AgentStats.[Non-Mandated] = nz(DSum("Occurrences", "dActivityCodeStat",
"ActivityCodeName = 'Break' and AgentLogin = '" & [AgentLogin]
& "' and dt_Datefield = #" & Format([AgentDate],'mm/dd/yyyy') & "#"),0)
;

Watch out for the use of double quotes and single quotes and how the above
gets word-wapped in this display! Also, I've assumed AgentLogin is a text
datatype. If it is numeric then remove the single quotes.

Regards

Andy Hull

 

[Kayda]

Thanks very much, that works.