Query Just won't work!

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Alright well I have this query I'm trying to run. Basically what I want to do is add up all the values of a column for which a statement is true and then update the sum to another field. Here is the query I'm trying to run

UPDATE 1234
SET [Hours On Seal Total] = Sum([hours on seal]
WHERE [Seal Number]=1

I want to set the field "hours on seal total" to be the sum of all the "hours on seal" fields for which the seal number is "1". I get this error:"You tried to execute a query that does not include the specified expression 'hours on seal total' as a part of an aggregate function". And it won't run
I can run it as a select statement

SELEC
SUM([hours on seal]
FROM 123
WHERE [Seal Number]=

And it returns the correct answer. What I need is for it to write that answer to a field in a table. HELP!?
 
Alright well I have this query I'm trying to run. Basically what I want to do is add up all the values of a column for which a statement is true and then update the sum to another field. Here is the query I'm trying to run.

UPDATE 1234
SET [Hours On Seal Total] = Sum([hours on seal])
WHERE [Seal Number]=1;

I want to set the field "hours on seal total" to be the sum of all the "hours on seal" fields for which the seal number is "1". I get this error:"You tried to execute a query that does not include the specified expression 'hours on seal total' as a part of an aggregate function". And it won't run.
I can run it as a select statement:

SELECT
SUM([hours on seal])
FROM 1234
WHERE [Seal Number]=1

And it returns the correct answer. What I need is for it to write that answer to a field in a table. HELP!?
Click to expand...

No Totals query can *ever* be updated in Access. And storing this sum
in your table Is A Very Bad Idea - what do you want to do? If there
are 3284 records in the table with Seal Number = 1, do you want all
3284 of them to contain the same sum value? You do realize that if you
were to delete any one of these records, or add a new record, or
change the value of [hours on seal] in any one of the records, you
would now have 3284 records WITH INCORRECT DATA and no easy way to
determine this fact?

Just calculate the total in a Totals query as needed. Storing it in
your table is simply *bad design*.
 
Try...

UPDATE 1234
SET [Hours On Seal Total] = (Select Sum([hours on seal])
FROM 1234 WHERE [Seal Number]=1)
WHERE [Seal Number]=1;


-----Original Message-----
Alright well I have this query I'm trying to run.
Click to expand...
Basically what I want to do is add up all the values of a
column for which a statement is true and then update the
sum to another field. Here is the query I'm trying to run.
UPDATE 1234
SET [Hours On Seal Total] = Sum([hours on seal])
WHERE [Seal Number]=1;

I want to set the field "hours on seal total" to be the
Click to expand...
sum of all the "hours on seal" fields for which the seal
number is "1". I get this error:"You tried to execute a
query that does not include the specified
expression 'hours on seal total' as a part of an aggregate
function". And it won't run.
I can run it as a select statement:

SELECT
SUM([hours on seal])
FROM 1234
WHERE [Seal Number]=1

And it returns the correct answer. What I need is for it
Click to expand...
to write that answer to a field in a table. HELP!?
 
Back
Top