I’m tasked with revamping a process to use Access instead of Excel due to the volume of data involved. This process has 4 years of data; 2021 – 2023 are completed and no changes will be made to these years.

I was thinking about having a history table that would contain each month (Jan – Dec) for each of the 3 years.

I was able to load 2021 and 2022 into separate tables. I made a copy of 2021 (and called it History) and added additional fields for the 12 months for 2022 and 2023. My history table has about 40 fields – 4 identifying fields and 36 month fields.

Since the history table is a copy of 2021, it is complete.

The next step is to move the 2022 data in the appropriate month field for the matching identifying fields (Employee ID, Location, Region, Job Code).

The end result is Employee ID ABC123 would have the 2022 data in the appropriate month where it matches the 4 identifying fields.

The goal is the history table will have 3 years of complete data for each Employee ID, Location, Region, and Job Code.

I’m not sure how to structure a query to load the 2022 and 2023 data into the History table. I thought this would be better for my process to only have to join 2 tables (History / 2024) instead of 4. Or would it be better to have the history table be vertical instead of horizontal.

Thanks for your help and taking the time to look at this question….