Discussions

 View Only

Trying to redesign a working process to compensate new data entry ( trouble with summary fields and inter-app relationships

  • 1.  Trying to redesign a working process to compensate new data entry ( trouble with summary fields and inter-app relationships

    Posted 10-15-2018 18:47
         I designed an app last year to be able to track Employee Hours they used on Customer Jobs under a department issued work order relating to that job(Customer, Jobs, Depts, And Workorders all have their own tables). From there the employee hours were used to calculate a pay scale based on certain criteria in an evaluation. I got the app to spit out the correct data but not with the relationships I thought I should have used.

         I have multiple tables: "Employees" captures employee data(DOB, Hire Date and Employee ID), "Employee Hours" tracks an employee's time on a work order. "Employee Pay scale" takes an employees efficiency from time on work orders over the year (employee Hours table) combines them with an evaluation from that year to calculate a pay scale. My issue is that I had to use the "employee" table to summarize the "Employee Hour Report"  for time they had in a given year. This placed a total column in my employees table for their total hours (total budget hours vs total actual hours in the year 2017).

        Now that 2018 has come to a fiscal year close for me, To duplicate this process for 2018 I have to summarize totals in the employee table adding two more fields every year. This then makes my data spread horizontally (One Employee gets multiple pay scales as time goes on) What I want to do is pull Employee ID down to the Pay scale table through the Employee Hour Table, Set a fiscal year, populate an employee's actual and budget hours from a report ran in the employee hours table based on the year selected. Essentially creating a new record annually for every evaluated employee. I have Employees related to Employee Hour Table by Employee ID. Then I have Employee Hours Related to Employee Pay Scale but cannot properly select related Employee from the Employees Hours table.