update query not updating correctly

[cys813]

I have an update query which updates a master table based on a sub table.

here's the SQL of the update query:

UPDATE tbl_ContractLedger AS C INNER JOIN tbl_CapitalPrs AS P ON C.ContractID = P.ContractID SET C.PRamt = NZ(DSum("Amount","tbl_CapitalPrs","ContractID='" & [C].[ContractID] & "'"),0)
WHERE (((P.[ApprovalCode])="n"));

I thought it was updating fine but then when I erased the data on Capital PR, it says 0 records updated.

For example(tbl_P), contractID=1 | $50
contractID=1 | $50

on tbl_C it should show $100 where contractID=1.
at first it seemed like it's working but when i erased both of those entry (i was expecting $0) i still have $100.

Since it's a DSUM shouldn't it update it to $0?

I know im probably not making much sense but I really can't get this to work. any suggestions?

 

[lespaul]

since there is no matching record in the detail table there's nothing to update from the main table....it may be that if you change it to a LEFT JOIN it might work, but can't guarantee that.

Leslie

Have you met Hardy Heron?

 

[cys813]

LEFT JOIN did not work. Thank you for your input though. is there no way to make this work then?

 

[cys813]

is there a difference between these two queries?

UPDATE tbl_ContractLedger AS C INNER JOIN tbl_CapitalPrs AS P ON C.ContractID = P.ContractID SET C.PRamt = NZ(DSum("Amount","tbl_CapitalPrs","ContractID='" & [C].[ContractID] & "'"),0)
WHERE (((P.[ApprovalCode])="n"));

vs

UPDATE tbl_ContractLedger AS C INNER JOIN tbl_CapitalPrs AS P ON C.ContractID = P.ContractID SET C.PRamt = NZ(DSum("Amount","tbl_CapitalPrs","ContractID='" & [C].[ContractID] & "'" AND ApprovalCode="n"),0)

tried both thinking they're the same thing but Im getting different result.

 

[lespaul]

yes there's a difference, having the Approval code in the two different places makes a difference in the logic used to perform both the calculation and the update.

Your best option would be to not include a Total field in the master table. If you need to know the total on a form or in a report you can just use a query to add up the details. Storing a calculated field breaks the rules of databases: Fundamentals of Relational Database Design

Leslie

 

[cys813]

Well the total amount needs to be updated after each transactio so I wanted to run the query after each entry to keep it updated. is there another way to do this?