9 Replies Latest reply on Nov 9, 2014 5:29 PM by taylorsharpe

    SQL question - getting query of query or getting there somehow....

    slayden@msn.com

      I have a large single table of closed REALTOR transactions in my area, with Name, PropertyAddress, CloseDate, ClosePrice, ListingAgent, SellingAgent, ListingBrokerage, SellingBrokerage fields.

       

      I need to

      1. pull reports on each agent, filtered by CloseDate as a date range, and summarizing by
        1. total closed transaction $,
        2. $ closed as Listing Agent,
        3. $ closed as Selling Agent,
        4. AverageListingPriceSold,
        5. AverageSellingAgentPriceSold
        6. Sort by highest to lowest in each of those.
        7. Group by Brokerages, so we can draw connections (ie agent our company is doing business with is at X company with Y top producer - get an introduction)

       

      In my previous Coldfusion life, I would have

      1. Queried the whole table for WHERE Person is ListingAgent OR SellingAgent and held it in memory
      2. Then, created sub-queries against that query to get the result of each of the questions above.

       

      What's the way to do this in Filmaker?

      (Still struggling with fm's SQL implementation)

       

       

      Thanks

      Scott