Update field from sum(anothertable.anotherfield)

  • Thread starter Thread starter YYZ
  • Start date Start date
Y

YYZ

I could have sworn I've done this in the past, but I can't get the syntax
right for the life of me. If anyone can spot what I'm doing wrong, or tell
me that you just can't do this, please feel free.

I have 2 tables. WC and WCM. WC is a Wall code. A wall code can have 0 or
more Materials(WCM). They are joined on a common field in both tables,
wall_code_id.

I need to set WC.report_extended_total = Sum(WCM.bid_proportion)

Here's what I've tried:

UPDATE WC INNER JOIN WCM ON
WC.wall_code_id = WCM.wall_code_id
SET WC.report_extended_total = Sum(WCM.bid_proportion)

This results in an error: "You tried to execute a query that does not
include the specified expression 'report_extentded_total' as part of an
aggregate function.

So then I tried:
UPDATE WC
SET WC.report_extended_total =
(SELECT Sum(WCM.bid_proportion)
FROM WCM
WHERE WCM.wall_code_id = WC.wall_code_id)
FROM WC

I tried it with and without the "FROM" line. Errors with "missing operator
in query expression...." and "operation must use an updatable query"

Any ideas on what I'm doing wrong?

Matt
 
Create the Sum query as it's own query, then a second query, based on the
Sum query, to perform the update.
 
[MVP] S.Clark said:
Create the Sum query as it's own query, then a second query, based on the
Sum query, to perform the update.
Click to expand...

Okay, I just tried that. I made a query, test1 which is this:
SELECT Sum(WCM.bid_proportion) AS BidProp
FROM WCM;

And then I tried to use it like this:
UPDATE WC SET WC.report_extended_total = ( SELECT BidProp FROM test1);

It says I must use an updatable query. Not to mention, I don't kow how to
tell BidProp to filter itself for just one wall_code_id...

Can you see what I'm doing wrong?

Thanks for the help, by the way.

Matt
 
Okay, I just tried that. I made a query, test1 which is this:
SELECT Sum(WCM.bid_proportion) AS BidProp
FROM WCM;

And then I tried to use it like this:
UPDATE WC SET WC.report_extended_total = ( SELECT BidProp FROM test1);

It says I must use an updatable query. Not to mention, I don't kow how to
tell BidProp to filter itself for just one wall_code_id...
Click to expand...

Access is *really* starchy about updatability and totals operations.
No Totals query, nor any query referencing a Totals query, is ever
updateable, and it cannot be made updateable!

The getaround is to use the DSum() function instead of a Sum query:

UPDATE WC SET WC.report_extended_total = DSum("[BidPropoportion]",
"WCM", "[wall_code_id] = " & <the ID that you want to use>)

John W. Vinson[MVP]
 
John Vinson said:
Access is *really* starchy about updatability and totals operations.
No Totals query, nor any query referencing a Totals query, is ever
updateable, and it cannot be made updateable!

The getaround is to use the DSum() function instead of a Sum query:

UPDATE WC SET WC.report_extended_total = DSum("[BidPropoportion]",
"WCM", "[wall_code_id] = " & <the ID that you want to use>)
Click to expand...

Thanks for that, John. I think I'm still missing a piece, though. I can't
specify the exact wall code id to use because I want to do this for a bunch
of them at one time. Right now I am getting a recordset that contains the
wall_code_id and the Sum(bid_proportion) all from the WCM table, then
looping through and issuing update statements. I was hoping to avoid that.
Unless I'm missing something obvious, even with the DSum function, I'm still
out of luck.

Thanks for the advice, though. That function may come in handy in the
future...

Actually, I COULD get a list of the wall_code_ids, and use wall_code_id IN
(list them out here) -- that might speed up the process...but then again, it
might just update every wall code record's report_extended_total field to
all the same value, which would be a sum of all the materials bid_proportion
fields for all the wall codes in the list. No group by is allowed in the
criteria portion of that function. Damn.

Matt
 
Thanks for that, John. I think I'm still missing a piece, though. I can't
specify the exact wall code id to use because I want to do this for a bunch
of them at one time. Right now I am getting a recordset that contains the
wall_code_id and the Sum(bid_proportion) all from the WCM table, then
looping through and issuing update statements. I was hoping to avoid that.
Unless I'm missing something obvious, even with the DSum function, I'm still
out of luck.
Click to expand...

If WC contains the wall_code_id field already, just use it:

UPDATE WC
SET WC.report_extended_total = DSum("[BidPropoportion]",
"WCM", "[wall_code_id] = " & wc.wall_code_id)


John W. Vinson[MVP]
 
John Vinson said:
If WC contains the wall_code_id field already, just use it:

UPDATE WC
SET WC.report_extended_total = DSum("[BidPropoportion]",
"WCM", "[wall_code_id] = " & wc.wall_code_id)
Click to expand...

Thanks so much! I can't believe I missed that one. That works perfectly,
at least from Access. It doesn't quite work from VB, but its better than
nothing.

Matt
 
Back
Top