Relate 2 records???

[Greg58]

Hi.  I am trying to create a simple single user DB to record purchasing and selling shares.  I will have a table for companies, and one for holdings (open trades).  So when I buy a number of shares from a company, it will be recorded the holdings table.  When I sell those shares I want to link the sale record to the purchase record, and have the details recorded in another table called completed trades.  The record in the holding table will disappear as well because they are no longer a current holding.

My question is, does this require the 3 tables or am I on the wrong track??

[comment]

1 hour ago, Greg58 said:

I want to link the sale record to the purchase record

I don't think that's practical, because a single sale can span several purchases, and conversely a single purchase can be split across several sales. The way I would approach this is to record purchases and sales in the same table, with sales being negative. Then calculate the balance in the table you keep calling Companies, even though it's proper name is Stock or Securities. That balance is your current holding. 

 

[Greg58]

Thank Comment.  I will give that a go