Calculated Fields

[Eeek]

Hi,
I have the following tables:
tblOrders
tblOrderLine
tblProducts

I have a sub-total field in the table tblOrderLine, in my form the user can select the product using a drop down list which is linked to the ProductID stored in tblOrderLine, they then enter a quantity (also stored in tblOrderLine), on the form i do not have a clue as to how it will calculate a sub-total of that purchase...pleaseeeeeeeee help i would really appreciate it! please please please

Also, even if i do get the sub-totals, how would that be added together for the Total field in tblOrders?! please please help!

[MMcCarthy]

Hi,
I have the following tables:
tblOrders
tblOrderLine
tblProducts

I have a sub-total field in the table tblOrderLine, in my form the user can select the product using a drop down list which is linked to the ProductID stored in tblOrderLine, they then enter a quantity (also stored in tblOrderLine), on the form i do not have a clue as to how it will calculate a sub-total of that purchase...pleaseeeeeeeee help i would really appreciate it! please please please

Also, even if i do get the sub-totals, how would that be added together for the Total field in tblOrders?! please please help!

The subtotal is a calculated field and as such should not be stored in the table.

In your form footer of the orderline form you can put an unbound textbox and set the control source as follows:

=Sum([ProductPrice]*[Qty])

[Eeek]

Oh, Thanks sooo much!, I have heard that calculated fields shouldnt be stored but i just didnt know what else to do, thanks a lot for your help!

[Eeek]

I tried doing that, however, it just displays "#error'
I don't know why it doesn't work; perhaps because it cannot look up the Price of the products from the tblOrderLine form?!
Could you let me know as to how i could get it to look up that value?! please please help

[nico5038]

Guess you can have multiple combinations of [ProductPrice] and [Qty] in your detail rows.
Best to first create a query to extract all rows from your table and add a field:
select [ProductPrice]*[Qty] as Amount, ...

Just "forget" to place that in the details section and use in the footer:
=Sum([Amount])
Also make sure the fieldname (see properties) is NOT [Amount] as Access will see that as a circular reference...

Nic;o)

[Eeek]

Sorry, I'm very very new to Queries, could you give me some more info please, i need to save all of these sub totals to give me a Total for the tblOrders, how would i do this, or would i need to store this field?

[NeoPa]

As per your other question (Querying database by date), you need to post the table MetaData.
Without this we can only guess at your fieldnames and as you don't seem experienced enough to translate this into workable SQL that isn't working for you.