I have three Tables:
Employee
Home Department
Rate
Home Department and Rate are child tables to Emplyoee and each have a value called "Effective Date", which determines when the Department or Rate of an Employee changed.
I would like to create a table for every employee with a record for each day of the year. Then I would look-up the Rate and Department that the Employee was working in at the given date based on the effective date. I would then have accurate cost allotment and rate calculations as Departments and Rates change during the year. Example
Employee Table Department Rate
Emp 1 Emp 1 - Dep1 - 1.1.2018 Emp 1 - Rate 1 - 1.1.2018
Emp 2 Emp 2 - Dep1 - 1.1.2018 Emp 2 - Rate 1 - 1.1.2018
Emp 3 Emp 1 - Dep2 - 1.2.2018 Emp 3 - Rate 2 - 1.1.2018
Emp 3 - Dep3 - 1.1.2018 Emp 1 - Rate 2 - 1.2.2018
Emp / Dept / Rate / Date Table
1.1.2018 Emp 1 Dep1 Rate 1
1.1.2018 Emp 2 Dep1 Rate 1
1.1.2018 Emp 3 Dep1 Rate 2
1.2.2018 Emp 1 Dep2 Rate 2
1.2.2018 Emp 2 Dep1 Rate 1
1.2.2018 Emp 3 Dep1 Rate 2
1.3.2018 Emp 1 Dep2 Rate 2
1.3.2018 Emp 2 Dep1 Rate 1
1.3.2018 Emp 3 Dep1 Rate 2
Any advice for creating this with a query so that as Employees or Rates or Depts change, the new table changes as well.
