Jump to content
Sign in to follow this  
jwnacnud

How to get a total of just one field

Recommended Posts

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 by Guest

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×

Important Information

By using this site, you agree to our Terms of Use.