DSum Error 1

[tacexpo]

I have an update Query based on two tables.
I would like to update tbl_BudMaster.VoucherAmt
where budgetkey is equal

here's the sql code i currently have

UPDATE tbl_BudMaster INNER JOIN tbl_Vregister ON tbl_BudMaster.BudgetKey = tbl_Vregister.BudgetKey SET tbl_BudMaster.VoucherAmt = DSum("budgetamt","tbl_Vregister")
WHERE (((tbl_BudMaster.BudgetKey)=[tbl_Vregister].[budgetkey]));

i have 3 records on tbl_Vregister.
Budgetkey | BudgetAmt
F0001 | $50
F0012 | $100
F0001 | $200

after i run the query, tbl_budMaster is being updated as follow

BudgetKey | VoucherAmt
F0001 | $350
..... |
F0012 | $350

why is it Dsuming fields that has a different budgetkey?
what am i doing wrong?

 

[PHV]

UPDATE tbl_BudMaster AS B INNER JOIN tbl_Vregister AS V ON B.BudgetKey = V.BudgetKey
SET B.VoucherAmt = DSum("budgetamt", "tbl_Vregister" ,"BudgetKey='" & B.BudgetKey & "'")

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

 

[tacexpo]

thank you!!!