Temporal Data Linkage

[MrGreg]

Hi all.

Been a big fan and usage of this community over the years for solutions.

This problem has me a bit stumped and are hoping you can assist.

Will do my best to describe it.

I need to link a column from one data table to another data table, based on dates of the first table.

For example, I have a number of monthly HR reports that show staff and their department.  The reports will differ overtime as staff change departments.

The second table I have is transaction data which contains the date of the transaction and the staff member.  

The problem I am trying to solve is, I need to link the transaction to the department that staff member was linked to at the time of the transaction.  In other words, the transaction date needs to query the corresponding HR table for that month. 

Eg.

HR Report Table - May 2022	Staff Name	Department
	John Doe	Department A
	Jane Smith	Department B

HR Report Table - June 2022	John Doe	Department C
	Jane Smith	Department B

Transaction Table	15 May 2022	John Doe	Department A
	15 June 2022	John Doe	Department C

Any suggestions how I can solve for this? 

[lukiz84]

Hi,

- merge all the HR Report Tables into one

- In the merged HR Report Table: Add a custom column containing Month & Staff Name

- In the Transaction Table: Add a custom column containing Month & Staff Name (must be the same as the custom column in the merged HR Report Table)

- Connect the Transaction table and the HR Report Table by the newly Created Columns

BR