Update query with a Sum()

[Jonathan Scott via AccessMonster.com]

Is it possible to update a column on one table that is the result of a sum of
another table's column? I have a table of accounts, which contain a balance
at a particular point in time, and that balance needs to be calculated from a
series of transactions on that account.

I have tried a "UPDATE account SET balance = (SELECT Sum(transactionAmount)"
style query only to be told by Access97 that it is not a possible update
query.

Any help would be appreciated. I would like to avoid doing it in code, rather
than SQL.

TIA,
Jonathan Scott

 

[Jonathan Scott via AccessMonster.com]

After searching around for a while, I found the following method, which still
does not work.

UPDATE [T_ACCOUNT] LEFT JOIN
(SELECT Sum([transactions].amount) As total, branchNumber, accountNumber
FROM [transactions]
WHERE '2001/04/26' >= [transactions].startDate
AND '2001/04/26' <= [transactions].finalizeDate
GROUP BY branchNumber, accountNumber) As Q
SET [T_ACCOUNT].balance = Q.total
WHERE [T_ACCOUNT].branchNumber = Q.branchNumber
AND [T_ACCOUNT].accountNumber = Q.accountNumber;

I've tried putting this into a DAO query class, but it keeps complaining
about the grammar, and highlighting the word SELECT.

Any help would be greatly appreciated!

Jonathan Scott

 

[Tim Ferguson]

I've tried putting this into a DAO query class, but it keeps complaining
about the grammar, and highlighting the word SELECT.

DAO uses an older version of Jet-SQL and there are lots of things you can't
do with it (mainly DDL and security, though). Using a subselect in a join
clause appears to be one one them.

Try creating a querydef for the subselect and reference that instead.

Or use the ADODB execute method, which uses a more advanced form of t-sql.

Hope that helps


Tim F

 

[Tim Ferguson]

WHERE '2001/04/26' >= [transactions].startDate
AND '2001/04/26' <= [transactions].finalizeDate

Sorry, I didn't spot this at first: this is T-SQL syntax and will not be
recognised by jet-SQL (unless these date fields are defined as text). For
DAO, you need something like

WHERE [transactions].startDate <= #2001-04-26#
AND #2001-04-26# <= [transactions].finalizeDate

Hope that helps


Tim F