How to get a total of just one field

[jwnacnud]

I have the following tables and fields.

[Orders]

OrderID

ShippingTotal

TaxTotal

[OrdersCharges]

ChargeID

Amount

OrderID

Type (can be Tax or Shipping)

For EACH order, how can I show the total tax and then the total shipping from the [OrdersCharges] table within the [Orders] table?

Each record in the [OrdersCharges] table can have MANY ordercharges. I tried creating a global text field called and TAX and populating it with "TAX". Then I did a self join where Type = Tax. Obviously they matched but this didn't work.

Thank you in advance.

Edited May 3, 2009 by Guest

[bcooney]

Why do you have an OrderCharges table if it's a one to one relationship? Just put tax rate, TaxTotal, ShippingTotal in Orders.

[jwnacnud]

Thank you, I have corrected the oversight in my explanation. It is actually a one to many relationship.

[comment]

There are several ways to approach this, but with only two types perhaps this may suitable:

1. Define an unstored calculation field cTaxType (result is Text) in the Orders table = "Tax".

2. Define a relationship between Orders and a new occurrence of OrderCharges (let's name this TO "TaxCharges") as:

Orders::OrderID = TaxCharges::OrderID

AND

Orders::cTaxType = TaxCharges::Type

3. Define a calculation field cTotalTax (result is Number) in the Orders table =

Sum ( TaxCharges::Amount )

Repeat these steps for the Shipping type.