Creating a relationship for date range find among multiple tables
I recently posted http://forums.filemaker.com/posts/31d8108476 and got a great answer but need to find a solution that does not use the summary fields. Working with several thousand records has made this option take too long. Plus, I need to work with fields from separate tables. I am going to simplify the database immensely so apologies if I am not clear.
Let's say I have:
3 tables located on an external server, located in a FM database. On the server, there is:
Employees table with name and employee ID.
PhoneSales table with a total of phone sales in a day, the date sold and employee ID.
OnlineSales table with a total online sales in a day, the date sold and employee ID.
What I want:
A sum total for a given employee of how many online and phone sales (combined) they made for a given time period. For instance, if I enter John Doe and 1/1/12...1/1/13, it would give me all his sales for that year.
I would also want to see the highest saler and lowest, rank all from high to low, etc.
I believe if I answer these, this will answer my questions on what I need to do with my other formulas/relationships but I can explain more about the database, if needed.
Thanks for all the help. Have a good one.