Running an update query based on aggregate result of a select quer

[Tim Long]

Hello, I am trying to run a query that updates a value in one table based on
the sum of values in another table and I don’t understand what I’m doing
wrong.

Below is a simplified version of what I’ve got... I have a query that gives
me the total of the salaries in the employees table (qryEmployeeCosts) and I
want to use the result from that query to update the Salaries field in the
Costs table using an update query (qryUpdateCosts). What I’m trying to do
seems simple enough but it doesn’t work and I don’t know what elementary
error(s) I’m making. I have shown two versions of qryUpdateCosts and the
result I get with each. Any help would be much appreciated.

EMPLOYEES
EmployeeID Employee Name Salary Year
1 John Smith 30000 2005
2 Sarah Jones 40000 2005
3 Jack Brown 50000 2005

COSTS
Salaries


qryEmployeeCosts
SELECT Sum([Salary]) AS SalaryCosts
FROM Employees;
Result: 120000

qryUpdateCosts
UPDATE Costs SET Costs.Salaries = [qryEmployeeCosts].[SalaryCosts];
Result: Enter Parameter Value.. qryEmployeeCosts.SalaryCosts

Or

UPDATE Costs, Employees SET Costs.Salaries = Sum([Employees].[Salary]);
Result: You tried to execute a query that does not include the specified
expression ‘Salaries’ as part of an aggregate function.

In my real tables, I have to specify a date range (for example, sum of
salaries where year = 2005 etc) and I didn't know how to sum the salaries for
a date range in one table and use that figure to update the Salaries field in
the Costs table all in the same query (ie an update query that also sums
values in another table), so decided to use two separate queries.

Many thanks in advance

Tim Long

 

[MGFoster]

Hello, I am trying to run a query that updates a value in one table based on
the sum of values in another table and I don’t understand what I’m doing
wrong.

Below is a simplified version of what I’ve got... I have a query that gives
me the total of the salaries in the employees table (qryEmployeeCosts) and I
want to use the result from that query to update the Salaries field in the
Costs table using an update query (qryUpdateCosts). What I’m trying to do
seems simple enough but it doesn’t work and I don’t know what elementary
error(s) I’m making. I have shown two versions of qryUpdateCosts and the
result I get with each. Any help would be much appreciated.

EMPLOYEES
EmployeeID Employee Name Salary Year
1 John Smith 30000 2005
2 Sarah Jones 40000 2005
3 Jack Brown 50000 2005

COSTS
Salaries


qryEmployeeCosts
SELECT Sum([Salary]) AS SalaryCosts
FROM Employees;
Result: 120000

qryUpdateCosts
UPDATE Costs SET Costs.Salaries = [qryEmployeeCosts].[SalaryCosts];
Result: Enter Parameter Value.. qryEmployeeCosts.SalaryCosts

Or

UPDATE Costs, Employees SET Costs.Salaries = Sum([Employees].[Salary]);
Result: You tried to execute a query that does not include the specified
expression ‘Salaries’ as part of an aggregate function.

In my real tables, I have to specify a date range (for example, sum of
salaries where year = 2005 etc) and I didn't know how to sum the salaries for
a date range in one table and use that figure to update the Salaries field in
the Costs table all in the same query (ie an update query that also sums
values in another table), so decided to use two separate queries.

Many thanks in advance

Tim Long

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

It's usually not a good idea to store a varying total value. That value
can be shown in a View (aka SELECT query). IOW, the Costs table should
really be a View that gives a summary of costs by year (or whatever date
range you desire).

If you must save the value (like, for historical reasons):

Here's a kicker - Access doesn't "like" non-updateable queries as the
source of a SET clause. A Sum() query is non-updateable & therefore
won't work. You have to save the data to a temp table & then use that
temp table in the UPDATE query (ugly, ugly).

This won't work (it will in other db engines):

UPDATE Costs SET Cost = (SELECT SUM(Salary) FROM Employees)

So use a make-table query to save the salary sum & then use that temp
table to update Costs. Then delete the temp table.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQtQ1g4echKqOuFEgEQKzbQCfV/7Z7G4+XgpAMJQwlPx+OO1/b3sAoIXR
p1JW0WuamQxZPeVr79DNBmsF
=VNYQ
-----END PGP SIGNATURE-----

 

[Tim Long]

I'll do that. Many thanks!

Hello, I am trying to run a query that updates a value in one table based on
the sum of values in another table and I don’t understand what I’m doing
wrong.

Below is a simplified version of what I’ve got... I have a query that gives
me the total of the salaries in the employees table (qryEmployeeCosts) and I
want to use the result from that query to update the Salaries field in the
Costs table using an update query (qryUpdateCosts). What I’m trying to do
seems simple enough but it doesn’t work and I don’t know what elementary
error(s) I’m making. I have shown two versions of qryUpdateCosts and the
result I get with each. Any help would be much appreciated.

EMPLOYEES
EmployeeID Employee Name Salary Year
1 John Smith 30000 2005
2 Sarah Jones 40000 2005
3 Jack Brown 50000 2005

COSTS
Salaries


qryEmployeeCosts
SELECT Sum([Salary]) AS SalaryCosts
FROM Employees;
Result: 120000

qryUpdateCosts
UPDATE Costs SET Costs.Salaries = [qryEmployeeCosts].[SalaryCosts];
Result: Enter Parameter Value.. qryEmployeeCosts.SalaryCosts

Or

UPDATE Costs, Employees SET Costs.Salaries = Sum([Employees].[Salary]);
Result: You tried to execute a query that does not include the specified
expression ‘Salaries’ as part of an aggregate function.

In my real tables, I have to specify a date range (for example, sum of
salaries where year = 2005 etc) and I didn't know how to sum the salaries for
a date range in one table and use that figure to update the Salaries field in
the Costs table all in the same query (ie an update query that also sums
values in another table), so decided to use two separate queries.

Many thanks in advance

Tim Long

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

It's usually not a good idea to store a varying total value. That value
can be shown in a View (aka SELECT query). IOW, the Costs table should
really be a View that gives a summary of costs by year (or whatever date
range you desire).

If you must save the value (like, for historical reasons):

Here's a kicker - Access doesn't "like" non-updateable queries as the
source of a SET clause. A Sum() query is non-updateable & therefore
won't work. You have to save the data to a temp table & then use that
temp table in the UPDATE query (ugly, ugly).

This won't work (it will in other db engines):

UPDATE Costs SET Cost = (SELECT SUM(Salary) FROM Employees)

So use a make-table query to save the salary sum & then use that temp
table to update Costs. Then delete the temp table.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQtQ1g4echKqOuFEgEQKzbQCfV/7Z7G4+XgpAMJQwlPx+OO1/b3sAoIXR
p1JW0WuamQxZPeVr79DNBmsF
=VNYQ
-----END PGP SIGNATURE-----

 

[John Vinson]

Hello, I am trying to run a query that updates a value in one table based on
the sum of values in another table and I don’t understand what I’m doing
wrong.
...

qryEmployeeCosts
SELECT Sum([Salary]) AS SalaryCosts
FROM Employees;
Result: 120000

qryUpdateCosts
UPDATE Costs SET Costs.Salaries = [qryEmployeeCosts].[SalaryCosts];
Result: Enter Parameter Value.. qryEmployeeCosts.SalaryCosts

The problem is twofold: for one, you can't refer to a query simply by
referencing its name, you need to *join* the query; but even if you
do, no Totals Query - nor any query containing a Totals Query - is
ever updateable. It's just a restriction of the JET database engine
software.

The getaround is to use the DLookUp function:

UPDATE Costs
SET Costs.Salaries = DLookUp("[SalaryCosts]", "[qryEmployeeCosts]");


John W. Vinson[MVP]

 

[MGFoster]

UPDATE Costs
SET Costs.Salaries = DLookUp("[SalaryCosts]", "[qryEmployeeCosts]");

Actually, the DSum() function. Still, a better answer than my rant
against that updateable "feature" of JET.