Not sure what you mean by "multiple realtors on the other side" - perhaps this is a join table? - but it sounds like you might benefit from one of two approaches:
1) You could create a dedicated reporting table where you create the records for your report so that you just have a "realtor" field instead of going back to the parent table. Then through scripting, just populate that table (either temporarily or for historical purposes).
2) Use a Virtual List technique to accomplish much the same task. Assemble the necessary values into a set of return delimited lists, then use calculated fields to display the values. (Summary fields will still work in this configuration.)
But basically, if I'm understanding your question correctly, you need to flatten your report into a single-table configuration so that "realtor" comes from a single place instead of from two parents to prevent the multiple summary effect.
It's one table. Please see attached excel
What I need to do is summarize on one layout
- Date range data where realtor acts as seller's agent
- Date range data where realtor acts as buyer's agent
In my old language (Coldfusion) I'd query all data, then do a query of queries routine adding a where clause filtering for above.
How do I do this in fmPro?
SFD_6_1_14_thru_6_15_14.xls 562.5 K
Same suggestion applies. Since you're trying to summarize the same record twice, you won't be able to use a standard summary report. You'll need to, in essence, duplicate each record so it can be separately summarized by agent (once by listing agent, once by selling agent). So I would probably use a Virtual List in this case. You could use ExecuteSQL to create each list, then just tack one onto the end of the other.
LOL. CF query of queries, gotta love it. Don't I wish FM had this. Nearest cousin is ExecuteSQL as Mike suggested.