Running sum total of commission earned

[topaznz]

Hi there Forum

I have set up a database to administer our product sales commission programme. Staff at clinics across the country sell items to clients and complete a line by line record of each individual sale they make which accumulates them 10% commission of the individual product wholesale price.

I am using the 2 databases below to enable a field to have a running balance of the commission earned in Programme Lines as below:

Clinic Personnel (containing <PERSONNEL ID> and <COMMISSION BALANCE>

Programme Lines (containing <COMMISSION> and <PERSONNEL ID>

The calculation I have used for the field displaying the total sum of commission is:

If ( Clinic Personnel::ID = Programme Lines::Personnel ID ; Sum ( Programme Lines::Commission ) )

This is not returning a value of anything - can anybody see where I am going wrong?

Many thanks in advance for any assistance you can provide.

Best regards

Tim

[Vaughan]

If the Programme Lines table is related to Clinic Personnel by Personnel ID (on both sides) then there is no need for the If() conditional, just use the Sum() to total up the related commissions through the relationship.

The relationship is doing all the work of working out which commissions to include.

[topaznz]

Hi Vaughan

Thank you so much for your reply and input - it worked perfectly!

Best regards

Tim