jc606

Thanks for that! I got it to work. Thank you for being patient with me. I really appreciate it.

Sorry about that. I will be picking the date, so all prices in that column would show prices for the related stock as of 12/31/2010.

my table has three fields: Date Ticker Price And its currently filled with all that data. With prices of various stocks at different dates. What I'd like to do is compare the price of a stock at one date to the price at another date. So, I was thinking I would create a new field to show/get the price of each stock for a specific date (e.g., 12/31/2010). So, If i'm looking at a record of AAPL at 1/31/2011 and it's price, then the last (new) field would show the price at 12/31/2010 (which would be pulled from another record).

Sorry, I'm having trouble understanding self-joins. I tried a tutorial, but I still don't seem to grasp the steps that I need. Basically, i want to do the following: table name:data date ticker price value1 12/31/2010 AAPL 300.25 1/31/2011 AAPL 325.73 300.25 2/14/2011 AAPL 350.11 300.25 12/31/2010 CRM 115.22 1/31/2011 CRM 120.15 115.22 Where "value1" supplies the price for the related ticker for the date 12/31/2010. Can you or someone else tell me the specific steps I need to take? Thanks again.

Can you or someone else elaborate on the first part (defining a self-join relationship)? Where and how can I do that?

Hi, I just started using FM yesterday (I used to use MS Access) and I got stuck. I have a table with data on stocks (e.g., price, date, ticker). For example: 12/31/2010 AAPL 300.25 1/31/2011 AAPL 325.73 2/14/2011 AAPL 350.11 I have three questions. 1) If i'm setting up a new calculation field, how do I retrieve a value from a field within the same table and using two criteria? For example, for the 3rd record (2/14/2011) above, let's say I want to get the price value where date=12/31/2010 and ticker=aapl which would be 300.25. So, for the 3rd record, the field would show 300.25. 2) Continuing with question 1, let's say I can get that value (300.25) which shows up on the 3rd record and now I want to run a calculation of (price of 2/14 - the value we just got) / price of 2/14. This provides the % return of 2/14 from 12/31. What's the step to make this part happen? 3) Lastly, is this an inefficient way of setting up, organizing, or calculating data? Thanks in advance. JP