AnsweredAssumed Answered

Summary of data

Question asked by FileMakerNovice on May 29, 2013
Latest reply on Jun 4, 2013 by philmodjunk

Title

Summary of data

Post

     Hello,

     I have a database that is fairly complex.  Let me provide an outline and description and then I will explain my issue.  I am leaving several related tables out to simplify what I am trying to do.  These are unrelated to the issue at hand.

     Tables:
     EmployeeStats
     Calls
     EmployeeInfo

     Relationship:
     EmployeeStats--EmployeeInfo
                          |
                        Calls

     EmployeeStats is related to EmployeeInfo:
     employeeID to employeeID

     EmployeeStats is related to Calls:  
     employeeID to employeeID
     StartDate<=Date
     EndDate>=Date
     OmitDates not equal to Dates

     So... Employee stats pulls from several other tables to provide statistics for an advisor.  For calls, we calculate a producitivity stat.  This stat is calculated by taking several fields, including, TimeOnCall, TimeTakingNotes, TimeResearching.  We then devide these by the total number of calls to get a productivity number.  

     My trouble is that my current method only provides a calculation of all the calls, regardless of what queue the employee is in.  The employee could be on T1, T2, Emergency call, ect.  In the "Calls" table, there is a "role" field.  All this data is imported into the database.

     My goal is to get a subsummary or something to calculate producitvity for each role.

     Let's take Employee A.  He had 40 calls.  Day 1, he took 5 in the T1 queue.  Day two, he took 10 in the T2 queue, etc.  In the reporting, each of these days has metadata, Time on call, TimeTakingNotes, etc.

     I currently, would only get the total of the time and then devide it by the total calls.  This is called "Productivity" and is located in EmployeeStats.

     What I'm wanting to do is get this productivity number for each Role.  Is this possible in my current setup?  How can I do this?  I've thought about summary fields and sort a list but there is no option for the fomula in summaries.  

     Any help is much appreciated.  Thanks.

Outcomes