Forum Discussion
Lookup Table change with time
Hi Anonymous
From your screenshot, i'm not clear about which are "budget group", "previous years","employee number".
"One of the employees here is moving to a different budget group (change) and I am not sure how to have the data reflect that without affecting previous years. "
How do you create this lookup table?
(from another table called "source table" here ?
does the "change" occur in "source table"?)
Best Regards
Maggie
v-juanli-msft wrote:Hi Anonymous
From your screenshot, i'm not clear about which are "budget group", "previous years","employee number".
"One of the employees here is moving to a different budget group (change) and I am not sure how to have the data reflect that without affecting previous years. "
How do you create this lookup table?
(from another table called "source table" here ?
does the "change" occur in "source table"?)
Best Regards
Maggie
I can see how my question was a bit unclear. So my expenses sheet is shown here. The employee number is used ina lookup table to find budget group.
Then we have the original lookup table that I provided. So from 2016-2018 Employee # 13472 was a part of STEER. In 2019 they will be moving to Training H. It is not possible to change their employee number because of how the data is reported. I am trying to find a way to make this changes while ensuring that they are still in the correct budget group for 2016-2018 (STEER).
Anyone? Thank you
Anonymous
Hi, my suggestion will be in your Budget Table insert Dates / Years .
Example :
Year Employee Group
2015 John B
2016 John C
2017 John C
2018 John D
With this you can calculate without problems.
Regards
Victor
This is definitely a plausible answer; however, this would create a very large lookup table. Especially as time goes on. I also need this accurate by the month. If there really is no other way I can definitely do it like that. But still looking for a solution that requires a bit less expanding.
I think I worded this kind of poorly initially. So allow me to restate my problem.
I have a lookup table set up like this
Employee ID Employee Name Budget Group Job title
1 John Training Trainer
2 Mary Management Manager
3 Greenberg Management Assistant
Employee ID's and Employee Names do not change. How can I make this Dynamic so that I can change someones info without affecting data where they are under a previous job title & Budget group?
The source data is formatted like this using a folder that updates monthly
Date Cost Element Employee ID Cost
x x x x
x x x x
x x x x
So for Example, John is going to move into management into Manager. But this goes into effect in March of 2019. I can't simply add a new selection like shown below because that will make it a many-to-many relationship. I also cannot change his budget group and job title because that will misrepresent his spending for previous years as a trainer and place that money spent on the manager budget when it shouldn't be. How may I approach this?
Employee ID Employee Name Budget Group Job title
1 John Training Trainer
1 John Management Manager
2 Mary Management Manager
3 Greenberg Management Assistant
Thank you,
Michael
