That must be easy!

[Claudio Rodrigues]

Sorry for the stupid question.
I am trying to do something simple using Access 2003.
I have two tables that I use for a simple ordering system
that I am trying to develop.
One table, TblOrders has these fields:
ID_Order (Primary Key, unique, auto-increment)
Date_Order
ID_Customer
ID_Shipper
Shipped
Date_Shipping
TrackingNumber
Total

Then I have another table TblOrderEntry where I store the
products and quantities ordered. Fields are:
ID_OrderEntry
ID_Order
ID_Product
Qty

On the TblProducts I have the ID_Product, Desc_Product
(description) and MSRP_Product (cost).

So what I want is on my Order form (with a subform for
the TblOrderEntry) is the TOTAL field to be calculated
automatically.
This means it needs to get all the records from
TblOrderEntry from the order being shown (that I already
see on the screen, works great) and retrieves from all
products on that order their cost (from the TblProducts)
and multiply by the QTY and SUM all.
How can I do that?? I think something like a SELECT
blabla FROM blabla AS TOTAL would do it?
I am not sure if the access gurus will understand what I
am trying to achieve but if you do let me know how to do
this.

Thanks!

 

[Andrew Smith]

What to do depends how you want to use the Total figure, but the one thing
you don't do is store the total in a table - any table!

You should use a query, DSum function or a report.

 

[John Vinson]

So what I want is on my Order form (with a subform for
the TblOrderEntry) is the TOTAL field to be calculated
automatically.

Calculate it on the Form (it's convenient to do so on the Subform
Footer; just add a textbox with a Control Source of =Sum([cost]).

But DON'T store it in the order table, unless you are consciously and
intentionally violating relational design! Since the total depends
totally <ahem> on values which exist in your table, it should be
calculated on the fly. If it's stored in the Order table and a record
in the OrderDetails table is changed or deleted, your total will now
be WRONG and you'll have no easy way to detect that it is.