update query help (2nd try) 2

[tacexpo]

I have two queries that updates currency
one to update the PRamt and other to update the VoucherAmt.

User fills out a Purchase Requisition form and at first by defaut, check "chkEST" is selected.
PRamount then would be updated /w Qry_PrAmt.

when user updates this form and checks "chkActual"(est will be set to no),
then VoucherAmt needs to be updated.

here are my sql codings

UPDATE tbl_BudMaster AS B INNER JOIN tbl_PurchaseRequisition AS V ON B.BudgetKey = V.BudgetKey SET B.PrAmt = nz(DSum("Amount","tbl_PurchaseRequisition","BudgetKey='" & B.BudgetKey & "'"),0)
WHERE (((V.Actual)=No));

and

UPDATE (tbl_BudMaster AS B INNER JOIN tbl_Vregister AS V ON B.BudgetKey = V.BudgetKey) INNER JOIN tbl_purchaseRequisition ON (B.BudgetKey = tbl_purchaseRequisition.BudgetKey) AND (V.PrNum = tbl_purchaseRequisition.PrNum) SET B.VoucherAmt = nz(DSum("budgetamt","tbl_Vregister","BudgetKey='" & B.BudgetKey & "'"),0)
WHERE (((V.DocNum)="N") AND ((tbl_purchaseRequisition.Actual)=Yes));

eventhough record is being changed from est to actual,
prAmt remains the same.

if no records is true where actual=no then shouldn't it
return 0? instead of previous record still there?
.

 

[lameid]

It almost sounds to me you expect a query to change something for something that does not match the criteria... This will not happen. Only if the criteria is matched will it do anything.

If that is not the case please answer these questions as I did not follow...

Which query are you expecting to have an effect?

What effect are you expecting?

What is the value of the field you are expecting an effect for?

 

[tacexpo]

like this query for example..

UPDATE tbl_BudMaster AS B INNER JOIN tbl_PurchaseRequisition AS V ON B.BudgetKey = V.BudgetKey SET B.PrAmt = nz(DSum("Amount","tbl_PurchaseRequisition","BudgetKey='" & B.BudgetKey & "'"),0)
WHERE (((V.Actual)=No));

lets say user creates 2 records. (rs=tbl_pr)
amount | Actual(checkbox)
50 | NO
50 | NO

which it updates tbl_BudMaster
PrAmT
100

when user updates the form and change it to actual
tbl_pr is updated
amount | Actual(checkbox)
49.99 | YES
40.00 | YES

which then should update tbl_budMaster to
PrAmt
0

am i making sense?

 

[lameid]

WHERE (((V.Actual)=No));

That means only if there is a checkbox equal to no will an update occur.

If you want it to update regardless, you can take it out. If you do that you can take that table out of the from clause too.

 

[tacexpo]

what im trying to say is...
shouldn't my update query return 0
if both records are true on WHERE (((V.Actual)=No)); ?

even after records are updated,
pramt is still at 100 instead of 0

 

[lameid]

Yes if there are literally two records in tbl_PurchaseRequisition.

If however, you are trying to update a larger table for a particular BudgetKey, you are missing that criteria.

 

[tacexpo]

this is what i have now..

UPDATE tbl_BudMaster AS B INNER JOIN tbl_PurchaseRequisition AS V ON B.BudgetKey = V.BudgetKey SET B.PrAmt = NZ(DSum("Amount","tbl_PurchaseRequisition","BudgetKey='" & B.BudgetKey & "'"),0)
WHERE (((V.Actual)=No) AND ((V.Estimate)=Yes)) and b.budgetkey = v.budgetkey;

PrAmt (budgetkey = f8001) is still 100
when no v.estimate is checked...

 

[lameid]

b.budgetkey = v.budgetkey

That is the same thing your join in the from clause is doing.

For example I guess you mean something like:

b.budgetkey = 1

Or

b.budgetkey = Forms!FormName!ControlName

You can still lose the second table.

 

[dhookom]

For the first query, I'm not sure why you don't use:

UPDATE tbl_BudMaster AS B 
SET B.PrAmt = nz(DSum("Amount", "tbl_PurchaseRequisition", 
"BudgetKey='" & B.BudgetKey & "' AND Actual = 0"),0)

I think you can do the same for the second query by combining the non updated tables into the domain of the DSum().

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[tacexpo]

actual is a Yes/No field.
am i misunderstanding? or
does 0 actually equal yes or no?

 

[PHV]

What about this ?
UPDATE tbl_BudMaster AS B INNER JOIN tbl_PurchaseRequisition AS V ON B.BudgetKey = V.BudgetKey
SET B.PrAmt = NZ(DSum("Amount","tbl_PurchaseRequisition","BudgetKey='" & B.BudgetKey & "' AND Actual=No AND Estimate=Yes"),0)

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

 

[lameid]

If you want to always update PrAmt even to 0 then the other methods are ok. If on the otherhand you want to leave it, if a field changes, then you would not want to combine it and then should stick to your individual queries.

Also the default for yes and no are -1 and 0 respectively. Honestly, any non 0 value would be considered true in a boolean test but if comparing against a stroed value it has to match exactly or -1. Personally I try to use yes/no or true/false because it is conceivable, although very unlikely, that future versions of Access may work differently.

 

[tacexpo]

that works perfectly. thank you guys for your help

 

[tacexpo]

another quick question.

seems like this query is running fine but i dont
have much data in yet.

is this one ok?

UPDATE (tbl_BudMaster AS B INNER JOIN tbl_Vregister AS V ON B.BudgetKey = V.BudgetKey) INNER JOIN tbl_purchaseRequisition ON (B.BudgetKey = tbl_purchaseRequisition.BudgetKey) AND (V.PrNum = tbl_purchaseRequisition.PrNum) SET B.VoucherAmt = nz(DSum("budgetamt","tbl_Vregister","BudgetKey='" & B.BudgetKey & "'"),0)
WHERE (((V.DocNum)="N") AND ((tbl_purchaseRequisition.Actual)=Yes));