What did you try to get it to work that didn't?
Why do you need a related table instead of just a field for the current stock value? Do you want to keep a separate record of each day's stock value?
Unless you want to do that, I don't see the need for a related table.
In your original table, what does one record represent? One Stock transaction? One stock? or ??? (I see you are tracking a cash balance as well as the purchase of a stock, but knowing the details helps suggest a relationship that will work for you if you need such a related table.)
Thanks for responding.
Heres A simplified version of my table
Ticker ( identifies the stock and I've managed to create a portal to see company info for given stock)
Cash (Nb if i take cash out I use a negative number although in reality this field is more or lesss a constant)
Cash balance (calculation:(cash+sell+divi)-(buy+fees)
To measure current performance at a given time I need to know the overall valuation of my portfolio. So I wanted to add two further fields:
Current Market Value (this value is always changing)
Valuation of Portfolio (calculation:Cash balance+Current Market Value)
My problem is where to put Current Market Valuation. I had tried a seperate table because if I entered the value as £2000 today and then tomorrow I enter it as £2200 my database adds them together rather than just using the current valuation. I had thought If I used a seperate table I could simply replace the existing value with the current one but I cannot get the calculation to work across the two tables.
At this stage I don't need to track performance over time (too complicated). What I'm trying to acheive is a simple form which if i enter todays market valuation would generate a figure that reflects the performance of my portfolio in relation to the amount of cash invested.
If you buy 200 shares of Stock X last week and purchase another 500 shares of stock x this week, do you have 2 records for stock x or one?
I would have two records.
And both need to access the same related record that reports the current stock value.
Define this relationship between your two tables:
Transactions::Ticker = StockValues::Ticker
Then calculation fields defined in Transactions can refer to StockValues::Value to get the current value of a single share of stock.
Thank you for helping. I have tried this but I cannot get it to work with the totals. The variable number is a total value of my current stock holdings. I must be doing something fundamentally wrong.
If table one has Two fields: Key (a serial number)
Numbers ( just a list of numbers)
How could I add up the numbers field then add this total to a number that each day may change to assertain a current total. Its the total which I worked with in my spread sheet but I can't seem to do this here.
I don't understand the structure of your data here. What do you mean by "a list of numbers"?
If the Transactions table records the number of shares purchased and "ticker" stores the unique stock symbol for that stock, then each Transactions record can use a calculation field, cTransactionValue, to compute the current value (for that transaction) as:
NumberOfShares * StockValues::Value
Then you can define a summary field as the Total of cTransactionValue
To see the total value of any given stock, either make this field a running total, (restart totals with each group) and use Ticker as the "group by field" or add a sub summary part "when sorted by" Ticker and put this summary field in the sub summary part.
With either option, you must sort the records by Ticker to group stock transactions by stock to see a sub total for each type of stock purchased.
I suppose what I'm trying to do is include a 'comparison' between the total purchase cost ( as worked out in the way you describe above) and todays stock value (taken from the total on my stockbrokers live statement).
I could of course just look at my stockbrokers valuation but it doesn't take into acoount any other factors eg cash, fees, dividends etc it just measures the performance of a portfolio in the context of current stock owned.
I get the feeling it would be more logical to deal with the individual stocks rather than the aggregate value of the portfolio. So I will have a go in that direction.
What I describe makes both calculations possible: the current value of the stocks purchased/sold in a given transaction record and the total of all such records--for either a specific stock or all stocks combined.