Queries to transform this data (1 Viewer)

PRTP

Registered User.
Local time
Today, 18:28
Joined
May 11, 2014
Messages
16
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
 

theDBguy

I’m here to help
Staff member
Local time
Today, 11:28
Joined
Oct 29, 2018
Messages
21,358
Hi. It's a little hard to decipher what you want from what you posted because the text alignments are somewhat off. Are you able to post a sample Excel file showing the raw data and the finish product? Thanks.
 

June7

AWF VIP
Local time
Today, 10:28
Joined
Mar 9, 2014
Messages
5,423
If the CROSSTAB output isn't what you want, then what do you want? Show example of desired result.
 

PRTP

Registered User.
Local time
Today, 18:28
Joined
May 11, 2014
Messages
16
Thanks for getting back to me


I've now uploaded a spreadsheet showing the data.


Re: June7
I wouldn't be surprised if some sort of crosstab ends up being the solution. I've used crosstabs before but I haven't managed to figure out how to correctly set it up for this data/objective.
 

Attachments

  • Example.xlsx
    8.9 KB · Views: 448

plog

Banishment Pending
Local time
Today, 13:28
Joined
May 11, 2011
Messages
11,611
Last() will never be part of a solution--any solution. It and First() are horrible functions that don't behave like sane people expect them to. Never use them.

A cross-tab is most likely the final step of this, but you've got a big problem in the middle:

Starting Data:
Team A 01/04/2011 12/01/2017 50%
Team B 01/04/2011 14/11/2015 50%

Expected Data:
01/04/2011 14/11/2015 50% 50% 0% 0% 0%
14/11/2015 12/01/2017 50% 0% 50% 0% 0%

That requires Team A data to be split out into 2 unique records:

Team A 01/04/2011 14/11/2015 50%
Team A 14/11/2015 12/01/2017 50%

SQL just doesn't easily behave that way. It can't really create records that don't exist. So, the easiest way for you to accomplish this, is by processing data. That means pointing some VBA code at your table, then have it run to spit out the required data to a temporary reporting table then use that table output what you ultimately want.
 

PRTP

Registered User.
Local time
Today, 18:28
Joined
May 11, 2014
Messages
16
Thanks Plog

That requires Team A data to be split out into 2 unique records:

Team A 01/04/2011 14/11/2015 50%
Team A 14/11/2015 12/01/2017 50%

Spot on, that's the complication that I think has stopped me from being able to write a query yet and I was nervous about First and Last.

I think you might have confirmed what I was fearing :eek: of SQL not easily behaving that way. But fingers crossed if anyone has any more ideas
 

June7

AWF VIP
Local time
Today, 10:28
Joined
Mar 9, 2014
Messages
5,423
Ugh! I see now the difficulty (really should have recognized from the post).

Query1
SELECT Example.Team, Example.From, Nz(DMin("From","Example","From>#" & [From] & "#"),[To]) AS NextDate, Example.Percentage FROM Example;

Query2
SELECT Example.Team, Nz(DMax("To","Example","To<#" & [To] & "#"),[From]) AS PrevDate, Example.To, Example.Percentage FROM Example;

Query3
SELECT Team, [From], NextDate, Percentage FROM Query1
UNION SELECT Team, PrevDate, [To], Percentage FROM Query2;

Query4
TRANSFORM Max(Query3.Percentage) AS MaxOfPercentage
SELECT Query3.FROM, Query3.NextDate
FROM Query3
GROUP BY Query3.FROM, Query3.NextDate
PIVOT Query3.Team;

First SELECT line in UNION defines field names. Use aliases as you see fit. The dates are converted to strings in the UNION so records don't order chronologically. Would have to convert back to date values. Let you clean this up.

Might be able to nest these into one very long SQL statement. Regardless, this will likely perform very slowly with large dataset. I would probably go VBA route.

Advise not to use From as a field name.
 
Last edited:

PRTP

Registered User.
Local time
Today, 18:28
Joined
May 11, 2014
Messages
16
Thanks June, some interesting code there :)

It doesn't quite give the right answer at the moment. But I think it has given me some ideas that I'll explore tomorrow.

Attached is a screen print of the results it's giving at the moment
 

Attachments

  • Test.png
    Test.png
    18.2 KB · Views: 452

June7

AWF VIP
Local time
Today, 10:28
Joined
Mar 9, 2014
Messages
5,423
Well, my test with the sample data provided returns the 3 records in your desired output.
 

PRTP

Registered User.
Local time
Today, 18:28
Joined
May 11, 2014
Messages
16
Hi June

I've given this more thought and annoyingly I still can't see why I'm getting odd results.

Any chance you could have a look please at the attached where I'm showing what each sub-query is resulting in on my computer?

I don't get why Query 1 is resulting in
From = 01/04/2011 To = 01/04/2011

instead of
From = 01/04/2011 To = 14/11/2015

Nor do I get why it's converting NextDate to text


Thanks
 

Attachments

  • Query Output.xlsx
    10.2 KB · Views: 356

June7

AWF VIP
Local time
Today, 10:28
Joined
Mar 9, 2014
Messages
5,423
May have something to do with international date structure. Where are you located?

I imported dates from the first Excel but now I notice the dates are not displayed same as in OP. OP shows international structure.

Notice in your first 2 queries the MM and DD are switched in NextDate and PrevDate. From and To agree with my output.

See if this helps http://allenbrowne.com/ser-36.html
 

Users who are viewing this thread

Top Bottom