Sum of a Field

[Abe]

Please help me!

What do I have to write in the Control Source of the
Field "OrderSubTotal" in a Form, to get the sum from the
Field "LineTotal" in Table "CustomerOrderDetail" Where
OrderID = OrderID


The Table "CustomerOrderDetail doesn't have a Filed
OrderSubTotal, However it has a Filed "LineTotal" witch is
Quantity * Unit Price.
Thanks

 

[Graham Mandeno]

Hi Abe

The usual way of doing this would be with a form (based on CustomerOrders)
and subform (based on a query of CustomerOrderDetail). The main and sub
forms would be linked by OrderID.

The query would include a *calculated* field:
LineTotal: [Quantity] * [Unit Price]
There is no need to have a field for this in your table.

In the footer of your subform, add a textbox named txtSubTotal with the
ControlSource:
=Sum([LineTotal])

This will display the subtotal for all the detail records displayed in the
subform (ie, only the current order).

If you want this value on your main form instead, then make the subform
textbox invisible, and set the main form textbox's Controlsource to:
=[sfmOrderDetails].Form![txtSubTotal]
(where sfmOrderDetails is the name of the subform control on your main form)

--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand

Return mail address is invalid in a vain attempt to reduce spam.
Feedback is welcome at: (e-mail address removed)
Please post new questions or followups to newsgroup.

 

[John Vinson]

Please help me!

What do I have to write in the Control Source of the
Field "OrderSubTotal" in a Form, to get the sum from the
Field "LineTotal" in Table "CustomerOrderDetail" Where
OrderID = OrderID

=DSum("[LineTotal]", "[CustomerOrderDetail]", "[OrderID] = " &
[OrderID])

or, if LineTotal is on the Form, you can put a textbox in the form's
Footer with a control source of

=Sum([LineTotal])