Update Query problem 1

[fly231]

Hi,

I ran into a problem while trying to write an sql update query in MS Access 2000.

As you can see in the code I'm trying to update a field in the SummaryResults table using another query.
But I'm getting this error saying "Operation must us an updatable query"

I would appreciate it very much if someone could help me with this.

UPDATE SummaryResults b  
SET b.[calls 9-12] = (select count(a.Time)
FROM [Itemisation] a,  SummaryResults b
WHERE
(a.Time>#12/30/1899 09:0:0#) AND 
(a.Time<#12/30/1899 12:0:0#) AND
a.UserCLI =b.UserCLI
GROUP BY a.UserCLI, b.UserCLI)

 

[jadams0173]

I had a similar problem one time and the way I fixed it was to create a table from the query within the update and then run the update off of that. Not sure that is the best or correct way but it worked for me.

 

[fly231]

Hi jadams0173,
Thanks for the quick response,

But I don’t understand you need to create another table. Plus if I create another table I will have to do the same select statement from that table as well.

I would greatly appreciate Any help on this matter

 

[fly231]

jadams0173 Thanks so much

You were right, I didn't see it before. but i manage do it by first putting the data in to a temp Table, and then using temp Table to update the actual Table.

Here's the code I used, so that this may help someone.

First query to put the data to the Temp table

SELECT count( a.Time), a.UserCLI, a.[Date] AS [Date] 
INTO tmp9to12calls
FROM [Itemisation] AS a
WHERE (a.Time>#12/30/1899 09:0:0#) AND 
(a.Time<#12/30/1899 12:0:0#)
GROUP BY a.UserCLI, a.[Date];

Second query to update the actual table

UPDATE SummaryResults AS b, tmp9to12calls AS c 
SET b.[calls 9-12] = c.Expr1000
WHERE b.UserCLI = c.UserCLI AND b.[Date]=c.[Date];