Create Table for each Day in a Year

[MagnaNoahMcBrid]

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.

[MagnaNoahMcBrid]

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.