Is Join Table Required?
My database holds information about Employees, the Departments they are assigned to and their compensation history.
I want to create a report showing payroll costs by department by Year and / or Quarter starting with a date entered by the User and I want the report to compare quarterly values side by side.
- For comparison purposes, I would like the data to appear in columns. e.g. 2010 > 2011 > 2012 OR Q1-2014 > Q2 -2014 > etc.
- Employees may need to appear in two deparments for the same quarter if they move from one department to another mid-quarter.
- Employees compensation rates may also change mid quarter.
I'm assuming I'll need three tables (Departments, Employees and Join_Employee_Transactions.) The join table will record effective dates of department reassignments and effective dates of compensation changes and amounts. What type of relationship do I need between the tables to account for mid-quarter changes?
What is the best way to create the columnar report comparing Quarters side by side? Should this be done in separate fields and if so, in which table? Might it require a 4th table with dedicated calc fields based on the other tables?
I'm at a loss about structuring this but fortunately I have the opportunity to set this up right from the start.
Thanks for any and all help.
Using FMPA 14.