AnsweredAssumed Answered

Creating a relationship for date range find among multiple tables

Question asked by FileMakerNovice on Jan 30, 2013
Latest reply on Feb 4, 2013 by FileMakerNovice

Title

Creating a relationship for date range find among multiple tables

Post

     Hello all,

     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.

Outcomes