Sum of a Field

  • Thread starter Thread starter Abe
  • Start date Start date
A

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
 
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.
 
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
Click to expand...

=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])
 
Back
Top