Joined UPDATE query

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
 
G

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Update Query 1
Union query to Excel from Access 1
Update Query Problem 1
IIf for individual record 6
Zero in "Count" in group-by query 4
Table Design Problem? 1
help for join 1
Query miscalculations, why? 4

Top