不是牛人不要进:求改ACCESS的语句怎么表达?
有2个表，AnswerPaper答卷（总分）表、AnswerPaperData答题表
AnswerPaper
------------------
guidex       Mark(试卷总分)
aaa``````17
bbb``````20

AnswerPaperData表：
QuestionGUID（题号）
Mark(该题得分)
-------
guidex     AnswerPaper     QuestionGUID     Mark(该题得分)
    1```````aaa   `````````001``````````2     --》   5
    2```````aaa``````````002``````````5
    3```````aaa``````````003``````````10
    4```````bbb``````````001``````````3     --》   5
    5```````bbb``````````003``````````7
    6```````bbb``````````004``````````10
我想把题号QuestionGUID=001   的得分都改成5分后更新答卷（总分）表，下面是重新计算答卷表总分分的更新语句：

UPDATE   AnswerPaper   As   AP   Set   mark   =(select   sum(Mark)   from   AnswerPaperData   as   D   where   D.AnswerPaper   =   AnswerPaper.guidex)
where   AP.guidex   in(Select   AnswerPaper   from   AnswerPaperData   where  
QuestionGUID   =   '001 ')

语句执行应该得到：
AnswerPaper
------------------
guidex       Mark(试卷总分)
aaa```````20
bbb```````22

在SQLServer2000下是对的，请问怎么用jet-sql写出来并能在ACCESS下执行啊？



------解决方案--------------------

--Access使用以下两种方法处理：

--1、用域函数处理：dsum()

UPDATE AnswerPaper AS T SET Mark = dsum( "Mark ", "AnswerPaperData ", "AnswerPaper= ' " & T.guidex & " ' ")


--2、把子查询的中间结果保存到临时表中，然后再用临时表和目标表关联更新。

--将中间数据生成临时表
SELECT AnswerPaperData.AnswerPaper, Sum(AnswerPaperData.Mark) AS Mark合计
into 新表
FROM AnswerPaperData
GROUP BY AnswerPaperData.AnswerPaper;

--用临时表关联要跟新的目标表更新
UPDATE AnswerPaper INNER JOIN 新表 ON AnswerPaper.guidex = 新表.AnswerPaper SET AnswerPaper.Mark = [新表].[Mark合计];


当然，还可以用VBA+SQL处理，但使用域函数是最简单的方法。
------解决方案--------------------
JET SQL不是T-SQL，在ACCESS中用域函数：
UPDATE AnswerPaper AS T SET Mark = dsum( "Mark ", "AnswerPaperData ", "AnswerPaper= ' " & T.guidex & " ' ")
在其它环境中先生成临时表，再替换
SELECT AnswerPaperData.AnswerPaper, Sum(AnswerPaperData.Mark) AS Mark合计
into TT
FROM AnswerPaperData
GROUP BY AnswerPaperData.AnswerPaper;

UPDATE AnswerPaper INNER JOIN TT ON AnswerPaper.guidex = TT.AnswerPaper SET AnswerPaper.Mark = [TT].[Mark合计];

------解决方案--------------------
如果你在外部访问MDB，只能拆分为两步:

--将中间数据生成临时表
SELECT AnswerPaperData.AnswerPaper, Sum(AnswerPaperData.Mark) AS Mark合计
into 新表
FROM AnswerPaperData
GROUP BY AnswerPaperData.AnswerPaper;

--用临时表关联要跟新的目标表更新
UPDATE AnswerPaper INNER JOIN 新表 ON AnswerPaper.guidex = 新表.AnswerPaper SET AnswerPaper.Mark = [新表].[Mark合计];
------解决方案--------------------
Access的Jet-SQL的update语句中不支持子查询，你还是变通一下吧，这是限制，不是技术问题。

如果你在外部访问MDB，只能拆分为两步:

--将中间数据生成临时表
SELECT AnswerPaperData.AnswerPaper, Sum(AnswerPaperData.Mark) AS Mark合计
into 新表
FROM AnswerPaperData
GROUP BY AnswerPaperData.AnswerPaper;

--用临时表关联要跟新的目标表更新
UPDATE AnswerPaper INNER JOIN 新表 ON AnswerPaper.guidex = 新表.AnswerPaper SET AnswerPaper.Mark = [新表].[Mark合计];
------解决方案--------------------
楼主主要是要解决多人使用时生成临时表名称的问题，