Please help how to calculate order detail

[tonysi]

Hi Everyone,

I am sorry if my post has been cover so many times. I am new with access and still learning and also my English is not that good. I really appreciate for helping me with my problem.

The problem that I am having is how to recalculate all order detail item. Sorry not sure how to explain
FORM
Main form = Tblorder : orderID, CustumerID, TotalSquare
Sub form = TblOrderdetail : orderdetailID, OrderID, itemname, unitprice, total

Example: if I set up the totalsquare FIRST = 10 and I select the the itemname, it will calculate the total = unitprice * totalsquare this work fine.

The problem:
I have 20 item in sub form orderdetail and every item was calculate based on totalsquare = 10. For some reason, I have to change the totalsquare = 20.
How do I make so that it will recalculate all 20 items in subform/orderdetail instead of deleting all item and re enter it again?

Sorry if my question wasn't clear and thank you for your help!

[jonh]

Use an update query

(create > query design > close show table > click SQL > paste code > Run)


UPDATE
TblOrderdetail
INNER JOIN Tblorder
ON Tblorder.orderID = TblOrderdetail.OrderID
SET
total = [unitprice]*[totalsquare]

Sometimes it's better to always calculate values and not store them in a table at all.
So any update to totalsquare displays the new total automatically without needing to update TblOrderdetail.


SELECT
TblOrderdetail.orderdetailID,
TblOrderdetail.OrderID,
TblOrderdetail.itemname,
TblOrderdetail.unitprice,
[unitprice]*[totalsquare] AS totalprice
FROM
Tblorder
INNER JOIN TblOrderdetail
ON Tblorder.orderID = TblOrderdetail.OrderID

[tonysi]

Hi

Thank you for your respond. I think I was not clear with my question.

I have attached sample database so it is easy to understand.

There 1 order there currently totalsquare = 50.
Total is calculate based on Unitprice * totalsquare

I have 4 products listed there. If I changed the totalsquare = 100 from 50, How do I make so all my products will recalculate the total.


Thank you for your help.


13280


Use an update query

(create > query design > close show table > click SQL > paste code > Run)


UPDATE
TblOrderdetail
INNER JOIN Tblorder
ON Tblorder.orderID = TblOrderdetail.OrderID
SET
total = [unitprice]*[totalsquare]

Sometimes it's better to always calculate values and not store them in a table at all.
So any update to totalsquare displays the new total automatically without needing to update TblOrderdetail.


SELECT
TblOrderdetail.orderdetailID,
TblOrderdetail.OrderID,
TblOrderdetail.itemname,
TblOrderdetail.unitprice,
[unitprice]*[totalsquare] AS totalprice
FROM
Tblorder
INNER JOIN TblOrderdetail
ON Tblorder.orderID = TblOrderdetail.OrderID

[jonh]

Well,you are using vba after update to write the value.
Unless you have a particular need to store values I would use a calculated field in a query as I said above, or
change the Total controlsource property to something like =[Quantity]*[UnitPrice]*[parent].[totalsquare]
but that seems to break the total row. (I guess total rows are new feature. I'm not familiar with it.)

(I would expect that if quantity = 0 the total should also = 0)

[tonysi]

Thank you Jonh.

This work as I expected.
UPDATE
TblOrderdetail
INNER JOIN Tblorder
ON Tblorder.orderID = TblOrderdetail.OrderID
SET
total = [unitprice]*[totalsquare]

There is a reasons why quantity is there and If i may ask some more question.
There are some product that will multiply by totalsquare and there are products that will multiply by quantity.

For example: lets assume that labour will be charge Unitprice * totalsquare and garbage also will charge by unitprice* totalsquare. The reset of product will be charge by unit price * quantity.

If I am correct the logic is

If productID = labour or productID = garbage than
total = unitprice * totalsquare
else
total = unitprice * quantity

I would have couple of product that will need to calculate the total = unit price * total square and the rest of product will calculate unit price * quantity.

My question is how do I put this criteria in query?

I think I can calculate only the product that are multiply by totalsquare by putting the criteria in Query update where productid = labour or garbage and it will only recalculate those to products. Am I correct? But would like to learn if I can update all the product with those 2 condition using update query.

The Other thing that I would like to know if this is not so much trouble for you, Is there anyway to do it in VB instead of using query update I would like to learn this if it is possible.

Thank you.

[jonh]

Multiplier: iif(quantity=0,totalsquare,quantity)

Set the field default values to 0 and if one is 0 use the other.