Update field in master record with a total of related records.

[brj1]

I have a table transformer_bank_Tbl.
example:
BankID-1 Location-5th Street Size-Blank

And I have a related table transformer_tbl
example:
TransformerID-1 Size-15 BankID-1
TransformerID-2 Size-15 BankID-1
TransformerID-3 Size-15 BankID-1

I need to run a query that updates the transformer_bank_Tbl.Size to the sum of transformer_tbl.size where the transformer_tbl.bankID = transformer_bank_tbl.BankID

example:
BankID-1 Location-5th Street Size-45

I can't get this to work. Here is an example of what I came up with. UPDATE Transformer_Bank_Tbl SET Transformer_Bank_Tbl.Size = Sum([Transformer_tbl].[Size]);

 

[lespaul]

maybe:

UPDATE transformer_bank_Tbl t1 INNER JOIN transformer_tbl t2 on t1.bankid = t2.bankid set t1.size = Sum(t2.size)

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[HarleyQuinn]

I've tried this several ways now and the only problem I'm coming across is the need for the aggregation in the second table. The easiest (and quickest to create by far, especially if this is a one off), is to make a table based on the ID and Sum(Size) of transformer_tbl, and do an update based on the value in that table. You can still write the SQL for it and if it's required on a regular basis you can set a macro to run all you queries.

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[PHV]

UPDATE Transformer_Bank_Tbl
SET [Size] = Sum("[Size]","Transformer_tbl","BankID=" & [BankID]);

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886