Update query using Sum function

[Guest]

Hi,

I have a databse with a customer table and three seperate account tables all
linked by CustID.

Each account table contains a field called profit. I am trying to use an
update query (I havle a blank profit field on the customer table) to
calculate the overall customer profit by summing up the account level profit
for all their accounts.

A Customer can appear in any or all of the account tables and can have more
than one record within each account table.

I cant get an update query to do this for me and am having to do this in two
steps. Any help would be greatly appreciated.

Thanks

Simon

 

[John Vinson]

Hi,

I have a databse with a customer table and three seperate account tables all
linked by CustID.

Each account table contains a field called profit. I am trying to use an
update query (I havle a blank profit field on the customer table) to
calculate the overall customer profit by summing up the account level profit
for all their accounts.

A Customer can appear in any or all of the account tables and can have more
than one record within each account table.

I cant get an update query to do this for me and am having to do this in two
steps. Any help would be greatly appreciated.

Thanks

Simon

No Totals query, nor any query including a Totals query, is ever
updateable in JET.

Either use the DSum() VBA function as a calculated field (rather than
a SQL sum query); or - probably better - recalculate the sums on
demand rather than storing this derived data in your table.

John W. Vinson[MVP]

 

[peregenem]

No Totals query, nor any query including a Totals query, is ever
updateable in JET.

Do you mean a SUM...GROUP BY? Reaplce the construct with the equivalent
SUM in a subquery and it will be updateable. Which reminds me, the OP
could be alluding to this:

CREATE TABLE Test (key_col INTEGER NOT NULL UNIQUE);
INSERT INTO Test VALUES (1);
INSERT INTO Test VALUES (2);
INSERT INTO Test VALUES (3);
CREATE VIEW TestView
AS
SELECT key_col, (
SELECT SUM(key_col)
FROM Test AS T2
) AS sum_key_col
FROM Test;

UPDATE TestView
SET Key_col = 99
WHERE Key_col = 1;
-- success, VIEW is updateable

DROP VIEW TestView;

UPDATE Test
SET key_col = (
SELECT SUM(T2.key_col)
FROM Test AS T2)
-- fails

The first UPDATE, which operates on the Query (VIEW), shows that a SUM
in a subquery does not prevent a Query from being updateable.

The second UPDATE, which operates on the base table (the Query/VIEW has
been dropped by this stage), shows that Jet/Access is not capable of
using a subquery in an UPDATE. The resultant error message 'Operation
must use an updateable query' doesn't seem appropriate: we are not
trying to UPDATE the value in the subquery so why should it matter
whether the subquery is updateable or not?

Whatever, the bottom line is, Jet/Access isn't clever enough to do an
UPDATE the standard way. You must use Jet's own UPDATE..JOIN syntax
which violates the standards by using an alias rather than the base
table (i.e. uses an alias which should create a working table which
would disappear after the SQL has been executed meaning the base table
should not be updated at all etc etc).

 

[John Vinson]

Do you mean a SUM...GROUP BY? Reaplce the construct with the equivalent
SUM in a subquery and it will be updateable.

In SQL/Server it will be; in logic and good sense it would be; in the
JET database engine it unfortunately isn't, in my experience. Any
GROUP BY operation, even in a subquery, breaks updatability. It's a
design misfeature.

John W. Vinson[MVP]

 

[peregenem]

In SQL/Server it will be; in logic and good sense it would be; in the
JET database engine it unfortunately isn't, in my experience. Any
GROUP BY operation, even in a subquery, breaks updatability. It's a
design misfeature.

Agreed. What I actually mean, though, is that often a GROUP BY query
can be re-written using a subquery i.e. remove the GROUP BY construct
and replace it with a subquery that does the same. Because the GROUP BY
is no longer present in the query/subquery, it becomes updateable.
Quick example:

SELECT CustomerID, COUNT(*) AS order_count
FROM Orders
GROUP BY CustomerID;
-- Is not updateable

SELECT DISTINCT T1.CustomerID, (
SELECT COUNT(*)
FROM Orders
WHERE CustomerID = T1.CustomerID
) AS order_count
FROM Orders AS T1;
-- Is updateable