0 Replies Latest reply on Mar 31, 2010 11:45 AM by pault

    Searching for Records within a Date Range which also meet other Criteria

    pault

      Title

      Searching for Records within a Date Range which also meet other Criteria

      Post

      Hi All,

      I’m a long time user of FileMaker but have only recently delved into advanced features. I went through FileMaker’s Training Series book over the past weeks. I’m using FileMaker 9 on Windows XP.

       

      Here’s my problem.

      I have a small single table FileMaker 9 database which I use to track the stock holdings of several mutual funds.

       

      My question is how to write a query which will show stocks a particular mutual fund has been buying over a date range –  that is, where the number of shares has increased in each of the last 2 (or 3 or 4) quarters.

       

      I can create a Find request for records between 2 dates. I can format the results as I want. But it’s finding the records which meet my criteria which I can’t figure out. In fact I don’t  even know if I should try to do this with a custom function or a script.

       

      A little background information. Stocks are uniquely identified by a CUSIP number.

      Mutual funds are identified by name. A mutual fund never has more than one record for a given stock in a given quarter. End of quarter dates only occur on 4 days of the year:

       

      3/31/****

      6/30/****

      9/30/****

      12/31/****

       

      Example

      Fund Name      Date                 Stock               CUSIP             Shares

      ------------------------------------------------------------------------------------

      ABC Fund       6/30/2009        Intel                  458140100      955,000

      ABC Fund       9/30/2009        Intel                  458140100      961,600

      ABC Fund       12/31/2009      Intel                  458140100      975,000

      ABC Fund       9/30/2009        Sealed Air        81211K100     961,600

      ABC Fund       9/30/2009        Xerox               984121103      222,100

      ABC Fund       12/31/2009      Xerox               984121103      222,100

      XYZ Fund        12/31/2009      Sealed Air        81211K100     1,250,000

      XYZ Fund        9/30/2009        Du Pont            263534109      6,500,000

      XYZ Fund        12/31/2009      Du Pont            263534109      7,000,000

       

      The query should find stocks which a mutual fund has increased buying in each of the quarters in the date selection range.

       

      In this example, the query would find 2 records where the number of shares increased in each of the quarters with a date range of 6/30/2009 to 12/31/2009.

       

      Fund Name      Date                 Stock               CUSIP             Shares

      ---------------------------------------------------------------------------------------------

      ABC Fund       9/30/2009        Intel                  458140100      961,600

      ABC Fund       12/31/2009      Intel                  458140100      975,000

       

      I realize this may be a complicated request, but if someone can point in the right direction it would be greatly appreciated.

       

      Thanks,

      PaulTenn