update problem

[someguy]

Hi,

I'm trying to update a row in a table. The value in this record must
be set to the sum of a column from another table. I can't seem to figure
this out, i keeps saying my query is not updateable. Any sugguestions.
Thanks in advance.

TIA

[Allen Browne]

Before you do this, could there be valid situations where the stored total
should be different from the sum in the other table? If not, don't take on
the responsibility to keep this total always right, regardless of how
records are added/edited/deleted in the other table. Not worth it.

If you want to do it anyway, you should be able to use a DSum() or a
subquery into the UPDATE clause of the action query, e.g.:
UPDATE tblOrder SET MyTotal =
(SELECT SUM(Amount) FROM tblOrderDetail
WHERE tblOrderDetail.OrderID = tblOrder.OrderID);

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"someguy" <in*********@yahoo.com> wrote in message
news:Xn*********************************@216.196.9 7.142...

I'm trying to update a row in a table. The value in this record must
be set to the sum of a column from another table. I can't seem to figure
this out, i keeps saying my query is not updateable. Any sugguestions.
Thanks in advance.

TIA

[Inderjit Rai]

Hi Allen,
Thanks for replaying. The problem i have is that the tables are not
related, there is no common field and therefore there is no join option.
Sorry if this sounds like a trivial question but i'm not too familiar
with Access.

Thanks,

Inderjit
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Allen Browne]

So how do you know which records to total?

If you just want the total of all records, it's exactly the same, without
the WHERE clause in the subquery. If there is some criteria you can specify,
use that as the WHERE clause in the subquery.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Inderjit Rai" <in*********@rogers.com> wrote in message
news:41**********@127.0.0.1...

Thanks for replaying. The problem i have is that the tables are not
related, there is no common field and therefore there is no join option.
Sorry if this sounds like a trivial question but i'm not too familiar
with Access.

[Inderjit Rai]

Hi Allen,

Yes I am trying to get the total for the whole column in the other
table. I tried it without the where clause and it stills says 'must be
an updateable query'. Here's my code;

Update Table1
Set Table1.num1 = (Select Sum(Table2.num2) from Table2)

Thanks again

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Allen Browne]

Then just try a DSum():

strSQL = "UPDATE Table1 SET Table1.Num1 = " & Nz(DSum("Num2", "Table2"),0) &
";"
dbEngine(0)(0).Execute strSQL, dbFailOnError

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Inderjit Rai" <in*********@rogers.com> wrote in message
news:41**********@127.0.0.1...

Hi Allen,

Yes I am trying to get the total for the whole column in the other
table. I tried it without the where clause and it stills says 'must be
an updateable query'. Here's my code;

Update Table1
Set Table1.num1 = (Select Sum(Table2.num2) from Table2)

Thanks again

[Inderjit Rai]

Hey Allen,

Dsum works. Thanks for all your help! Let me if you ever need help
with DB2 or Cystal Reports.

Inderjit

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!