Condensing a history (1 Viewer)

PRTP

Registered User.
Local time
Today, 22:54
Joined
May 11, 2014
Messages
16
Hi


Pretend I've got


01/01/2000 - 01/01/2004 33 players in squad

01/01/2004 - 01/01/2007 25 players in squad

01/01/2007 - 01/01/2009 25 players in squad

01/01/2009 - 01/01/2012 47 players in squad



With how it didn't really change on 01/01/2007, is it difficult to write a query that would condense this to:


01/01/2000 - 01/01/2004 33 players in squad

01/01/2004 - 01/01/2009 25 players in squad

01/01/2009 - 01/01/2012 47 players in squad




I've uploaded a spreadsheet with another example



Thanks
 

Attachments

  • Access Question.xlsx
    8.8 KB · Views: 76

CJ_London

Super Moderator
Staff member
Local time
Today, 22:54
Joined
Feb 19, 2013
Messages
16,553
according to your data on 1/1/2009 you had both 25 and 47 players. same issue for 1/1/2004 correct your data to what you really mean first.

And to be clear, is this a one off tidying up exercise or an ongoing requirement and how realistic is the data? For example is this repeated for many teams?

At the moment the only way I can suggest is a manual process making use of a find duplicates query
 

PRTP

Registered User.
Local time
Today, 22:54
Joined
May 11, 2014
Messages
16
Thanks CJ London


That's what the data is like unfortunately. It's as if I'm expected to take the STOP_DATE with a pinch of salt. Or another of way of seeing it is, if the two weren't equal then I would be expected to intepret it as being a SQUAD_SIZE of zero for one day.



This "condensing problem" is happening for many teams.
This tidying up will need doing each time I get this "poor data".


I get the impression that my expectations of Access queries were a bit too high and I might have to resort to VBA to cleanse it.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 22:54
Joined
Feb 19, 2013
Messages
16,553
you haven't really clarified all the possible exceptions so the only way I can suggest is based on there only every being two records that are next to each other per your example - and since you haven't expanded on the data, I can't take the team values into account. You have also added another point, that if there is a a gap - but no indication as to whether that is what you want or not, so that also is something I can't work with

So my basic suggestion, based on the example data provided is as follows

Code:
SELECT A.FirstDate AS FromDate, nz(B.lastDate,A.LastDate) as ToDate, A.SquadSize
FROM myTable A LEFT JOIN myTable B on A.LastDate=B.FirstDate AND A.SquadSize=B.SquadSize
ORDER BY A.FirstDate

Good luck with your project, but I'm not able to devote any more time at present
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:54
Joined
Oct 29, 2018
Messages
21,358
I get the impression that my expectations of Access queries were a bit too high and I might have to resort to VBA to cleanse it.
Hi. I took the data from your Excel sample file and created a table like so:


Then, I created a query using this SQL:
Code:
SELECT Players.team, Players.startdate AS start, IIf([players].[players]=[players_1].[players],[players_1].[stopdate],[players].[stopdate]) AS stop, Players.players AS qty
FROM Players LEFT JOIN Players AS Players_1 ON Players.stopdate = Players_1.startdate
WHERE ((([players].[players]=Nz([players_1].[players],0))=False));
and got the following result:


Hope it helps...
 

Attachments

  • players.PNG
    players.PNG
    10.8 KB · Views: 205
  • query.PNG
    query.PNG
    7.5 KB · Views: 197

CJ_London

Super Moderator
Staff member
Local time
Today, 22:54
Joined
Feb 19, 2013
Messages
16,553
what happened to row 2 start date?:)
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:54
Joined
Oct 29, 2018
Messages
21,358
Ah, I guess it needs a little bit of tweak (maybe). Thanks.
Okay, this is starting to get a little bit complicated and will probably run slow. But I ended up with this:
Code:
SELECT Players.team, Players.startdate AS start, IIf([players].[players]=[players_1].[players],[players_1].[stopdate],[players].[stopdate]) AS stop, Players.players AS qty
FROM Players LEFT JOIN Players AS Players_1 ON Players.stopdate = Players_1.startdate
WHERE ((([players].[players]=(select a.players from players a where a.stopdate=players.[startdate])) Is Null Or ([players].[players]=(select a.players from players a where a.stopdate=players.[startdate]))=False));
With this result:


I hope it's close enough...
 

Attachments

  • newquery.PNG
    newquery.PNG
    7.7 KB · Views: 190

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:54
Joined
May 7, 2009
Messages
19,169
Code:
SELECT DISTINCT (SELECT TOP 1 T1.FromDate From Players AS T1 WHERE T1.NumberOfPlayer = Players.NumberOfPlayer ORDER BY t1.FromDate ASC) AS FromDate, (SELECT TOP 1 T2.ToDate FROM Players As T2 Where T2.NumberOfPlayer = Players.NumberOfPlayer Order By T2.ToDate Desc) AS ToDate, Players.NumberOfPlayer 
FROM Players
ORDER BY 1, 2;
 

PRTP

Registered User.
Local time
Today, 22:54
Joined
May 11, 2014
Messages
16
Thanks everyone especially DBGuy



It does the job great, but I'll spend a bit longer trying to understand why it's working
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:54
Joined
Oct 29, 2018
Messages
21,358
Thanks everyone especially DBGuy

It does the job great, but I'll spend a bit longer trying to understand why it's working
Hi. Please don't ask me to explain it. I just wanted to give it a try just to see if it can be done using just a query, without using code, but it can get confusing and probably run slow. So, in the end, you might still end up using code, if the drawbacks are not acceptable. Okay, I guess I could try to explain the query, if you insist.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 18:54
Joined
May 21, 2018
Messages
8,463
Any chance your squad size can come back down?

Code:
01/01/2000 - 01/01/2004 33 players in squad
01/01/2004 - 01/01/2007 25 players in squad
01/01/2007 - 01/01/2009 25 players in squad
01/01/2009 - 01/01/2012 47 players in squad
01/01/2010 - 01/01/2004 33 players in squad

Because I think that you would have to code that.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:54
Joined
May 7, 2009
Messages
19,169
eell majop is right it will only test on two occurrence of same score. my query will test to unlimited number of occurrence and will pass without ever modifying the query.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 18:54
Joined
May 21, 2018
Messages
8,463
eell majop is right it will only test on two occurrence of same score. my query will test to unlimited number of occurrence and will pass without ever modifying the query

But I would think you only want to combine consecutive values. So this
Code:
01/01/2000 - 01/01/2001 33 players in squad
01/01/2001 - 01/01/2002 25 players in squad
01/01/2002 - 01/01/2003 25 players in squad
01/01/2003 - 01/01/2004 25 players in squad
01/01/2004 - 01/01/2005 47 players in squad
01/01/2005 - 01/01/2006 33 players in squad
01/01/2006 - 01/01/2007 25 players in squad

Becomes
Code:
01/01/2000 - 01/01/2001 33 players in squad
01/01/2001 - 01/01/2004 25 players in squad
01/01/2004 - 01/01/2005 47 players in squad
01/01/2005 - 01/01/2006 33 players in squad
01/01/2006 - 01/01/2007 25 players in squad

I do not think any of the queries handles that.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:54
Joined
May 7, 2009
Messages
19,169
no sir i'm not combining consecutive value.
first part (outer select) returns the Distinct player number (no dups).
first inner loop get the earliest date for that number.
second inner loop get the latest date for that number.
attached is a sample with your data.
 

Attachments

  • combine.zip
    18 KB · Views: 61

MajP

You've got your good things, and you've got mine.
Local time
Today, 18:54
Joined
May 21, 2018
Messages
8,463
no sir i'm not combining consecutive value
Unless I am reading this wrong, you should be combining only consecutive. Your result is
Code:
FromDate	ToDate	NumberOfPlayer
01/01/2000	01/01/2006	33
01/01/2001	01/01/2007	25
01/01/2004	01/01/2005	47
That suggests that there was 33 players on a squad from 2000 to 2006 and 25 players from 2001 to 2007, which is not correct.

The OP will have to state what is correct, but does not seem logical to me.

Code:
01/01/2000 - 01/01/2001 33 players in squad
01/01/2001 - 01/01/2004 25 players in squad
01/01/2004 - 01/01/2005 47 players in squad
01/01/2005 - 01/01/2006 33 players in squad
01/01/2006 - 01/01/2007 25 players in squad
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:54
Joined
May 7, 2009
Messages
19,169
the last resort probably is VBA.
see form Players
 

Attachments

  • combine.zip
    26.8 KB · Views: 77

Users who are viewing this thread

Top Bottom