Hi, I don't think I'm quite at intermediate level yet with writing queries so I am a bit out of depth with this one.
I've got data that tells you
Team A was doing 50% of the work between 01/04/2011 and 12/01/2017
Team B did the other 50% between 01/04/2011 and 14/11/2015 but on 14/11/2015 they were replaced by Team C
Team A eventually lost the work and were replaced by splitting the work into Team D and Team E
Team From To Percentage
Team A 01/04/2011 12/01/2017 50%
Team B 01/04/2011 14/11/2015 50%
Team C 14/11/2015 05/03/2018 50%
Team D 12/01/2017 05/03/2018 25%
Team E 12/01/2017 05/03/2018 25%
With this structure it's difficult to see what % of the work went to each team at any point in time.
So I want to be able to transform this into:
From To Team A Team B Team C Team D Team E
01/04/2011 14/11/2015 50% 50% 0% 0% 0%
14/11/2015 12/01/2017 50% 0% 50% 0% 0%
12/01/2017 05/03/2018 0% 0% 50% 25% 25%
So far the best I've been able to do involves a Cross Tab query with the Last function and pasting that query's results into some formulas in Excel to arrive at the table I'm wanting.......Very messy!
Could anyone please point me in the right direction for how to transform the data via Access queries?
Thanks
I've got data that tells you
Team A was doing 50% of the work between 01/04/2011 and 12/01/2017
Team B did the other 50% between 01/04/2011 and 14/11/2015 but on 14/11/2015 they were replaced by Team C
Team A eventually lost the work and were replaced by splitting the work into Team D and Team E
Team From To Percentage
Team A 01/04/2011 12/01/2017 50%
Team B 01/04/2011 14/11/2015 50%
Team C 14/11/2015 05/03/2018 50%
Team D 12/01/2017 05/03/2018 25%
Team E 12/01/2017 05/03/2018 25%
With this structure it's difficult to see what % of the work went to each team at any point in time.
So I want to be able to transform this into:
From To Team A Team B Team C Team D Team E
01/04/2011 14/11/2015 50% 50% 0% 0% 0%
14/11/2015 12/01/2017 50% 0% 50% 0% 0%
12/01/2017 05/03/2018 0% 0% 50% 25% 25%
So far the best I've been able to do involves a Cross Tab query with the Last function and pasting that query's results into some formulas in Excel to arrive at the table I'm wanting.......Very messy!
Could anyone please point me in the right direction for how to transform the data via Access queries?
Thanks