update query with sum and group by

I have a quiz Q & A database (MS Access), where members can ask and answer questions and also vote on other answers and questions.

sample data and table structure / fields:

------------
questions
------------
q_id      q_title                topic_name      member_id
1         why is?                 Math                     17
2         what for?               Physics               20
3         How about?            Math                  18
4         and this?               Biology                 20
5         also by?                Physics               18


-----------
answers
-----------
a_id   answer_text      q_id      member_id
1       it is!                      1             18
2       not bad                 2             20
3       for me                  4              17
4       you are                  1             17
5       likely to                  3             18


-----------
avotes
-----------
vote_id   a_id     member_id     vote
1              4              18               1
2              3              20               1
3              1              17               1
4              4              17              -1
5              2              18               1


---------------------------------------------
qvotes
---------------------------------------------
vote_id   q_id     member_id     vote
1              4              18               1
2              3              20               1
3              1              17               1
4              4              17              -1
5              2              18               1


------------
members
------------
member_id      member_name    total_votes
17                        Dave                      ?
20                        Simon                     ?
18                        Alice                       ?


I have a query that gives me the total votes for each member (total of their votes from avotes and qvotes):

SELECT member_id, SUM(vote) as tvotes FROM
(SELECT member_id, Vote FROM avotes UNION ALL SELECT member_id, Vote FROM qvotes)
GROUP BY member_id

I have now created a total_votes field and want to fill this field with the tvotes that i can see in the previous query.

I tried this but its not working, i get syntax errors:

UPDATE members AS m, (Select member_id, sum(vote) as tvotes from (select member_id, Vote from avotes Union all select member_id, Vote from qvotes) Group by member_id AS v
SET m.total_votes = v.tvotes
WHERE m.member_id = v.member_id

and also this;

UPDATE members AS m INNER JOIN ((Select member_id, sum(Vote) as tvotes from (select member_id, Vote from avotes Union all select member_id, Vote from qvotes) Group by member_id) as v ON m.member_id = v.member_id
SET m.total_votes = v.tvotesStart Free Trial