Q:Calculation

[Edwik]

Hi All.

I have 3 tables in Access:
tblCurrency (Fields: IDCurrency, CDate, CRate)
tblOrder (Fields: IDOrder, OName, ODate)
tblCalculation (Fields: IDCal, Rate1, Rate2, Rate3, COrder)
IDOrder is related to COrder with "One to Many" connection.

I have Form frmOrder, based on tblOrder. For Example Form has 1 record:
IDCal OName ODate
1 XXXX 2005.10.24

For this record related table has 2 records:
IDCal Rate1 Rate2 Rate3 COrder
1 10 3.5 15 1
2 6 4.2 12 1

tblCurrency for Exapmle have one record too:
IDCurrency CDate CRate
1 2005.10.24 0.2

In frmOrder I have textBox txtRate. Here I need to be calculated:

=Sum (Rate1*(CRate from tblCurrency Where tblCurrency.Cdate = tblOrder.Odate)+Rate2*Rate3 for Each record related to tblOrder).
By Exapmle: (10*0.2+3.5*15)+(6*0.2+4.2*12)=106.1

Please help with expression.
Thank You in advance.

 

[Len Boorman]

Think you need to post more information such as the purpose of the Various Rates

Normally I suggest it would be practice to calculate cost of each order line first and the Sum to obtain the value of the order.

The value of an Order line basically is = Item Cost * Quantity * Conversion Rate

Having then a value for each order line you can sum the order line values for an order

Why do you have different rates and what do they mean

L

 

[Pat Hartman]

Calculations that include addition/subtraction as well as multiplication/division should include parentheses to indicate calculation order. Although your calculation actually returns the expected amount, it is better practice to be explicit. I would use:
((10*0.2)+(3.5*15))+((6*0.2)+(4.2*12))=106.1

Add a footer to the form and add a control with the expression:
=Sum(expression)
If you don't have all the data you need to perform the calculation, you need to change the recordsource of the form to be a query that joins to the appropriate tables to obtain all the operands you need.

 

[Edwik]

I have solve a problem using NZ and Dlookup functions!

Thank You for help.