AnsweredAssumed Answered

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

Outcomes