# Selective Calculation from field in different table

Question asked by MauriceG on Oct 24, 2014
Latest reply on Nov 2, 2014 by MauriceG

### Title

Selective Calculation from field in different table

### Post

I have a database that I use to manage some investment accounts and that contains, amongst others, the two tables below. These two tables are linked through a field called Year, located in a table called Main Table.

EQUITIES (only relevant fields shown)

 Account  No. Company Name Number of shares purchased Date of Purchase Cost per share Income (per share) from selling options on shares Date of Sale 2. ABC 400 Sept. 20, 2014 1.35\$ 0.38\$ 1. DEF 300 Sept. 15, 2014 1.50\$ 0.23\$ 1. ABC 500 Sept. 1, 2014 1.25\$ 1.02\$ 1. ABC 125 April 1, 2014 1.15\$ 1.00\$(This is the total proceeds from the sales of options made on April 2 and April 27, 2014.) July 1, 2014

OPTIONS (only relevant fields shown)

 Account No. Company Name Number of Options sold Date of Sale of Options Sale Proceeds per each option Expiry Date of Option 2. ABC 400 Sept. 22, 2014 0.38\$ 1. DEF 300 Sept. 18, 2014 0.23\$ 1. ABC 500 Oct. 10, 2014 0.34\$ Nov. 8, 2014 1. ABC 500 Sept. 10, 2014 0.68\$ Oct. 8, 2014 1. ABC 125 April 2, 2014 0.75\$ April 22, 2014 1. ABC 125 April 27, 2014 0.25\$ June 20, 2014 1. DEF 400 Sept. 15, 2014

I need to set up a calculation in the field Income (per share) from selling options on shares (table EQUITIES) that ensures the following:

the total amount in that field must add up the sales proceeds of options sold (table OPTIONS) but only as these proceeds relate

a. to equities that have not been sold yet (i.e. the Date of Sale field in the table EQUITIES is empty)?

b. to equities held in the same account; and

c. to equities of the same company.

As an example,  the 500 ABC shares purchased on Sept. 1, 2014 should show in the field Income (per share) from selling options on shares (table EQUITIES) a total of 1.02\$, and not 1.40\$ as the difference of 0.38\$ comes from account no. 2, not account no 1.

Sorry for the long explanation. I hope it is clear enough and that someone will be able to help because I have no clue how to create this calculation.

Thanks.

MG