不是牛人不要进:求改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合计];
[解决办法]
楼主主要是要解决多人使用时生成临时表名称的问题，

可以用用户名+时间 OR 用唯一ID来作为临时表名称