Calculate total when updating field according to different percentages of child table.

[JCarlos]

First of all I want to say thank you.

I can not find the solution to what I expose below, I hope you can help me.

I have a table "SALES" with the columns:

[ID], [Customer], [Date], [% General discount], [Total]

On the other hand I have the "SALES DETAIL" table related to the "SALES" table with the columns:

[ID], [Sales ID], [Product], [Units], [Price], [% discount], [Subtotal], [Tax].

In the column [Tax] the tax of each product is collected, these can be different as 21%, 10%, 4%... and there can be multiple rows with the same [Tax] and [Sales ID].

The [Subtotal] column calculates ([Units]*[Price]-[%Discount]) but not the [Tax], this must be calculated after the [%General Discount] of the "SALES" table.

2 discounts can be applied: [% Discount] for each row of the "SALES DETAIL" table and [% General Discount] for the set of rows of the same SALE

 

I want to make a function on the [Total] column,

so that in the SALES form I can calculate the [Total] of the sale in real time, how do I update the [% General Discount].

([Total] must calculate the sum of [Subtotal] - [% General Discount]+(the different [Taxes] that may exist))

Thanks for your support.