Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-15-2019, 07:23 AM   #1
PRTP
Newly Registered User
 
Join Date: May 2014
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
PRTP is on a distinguished road
Condensing a history

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
Attached Files
File Type: xlsx Access Question.xlsx (8.8 KB, 12 views)

PRTP is offline   Reply With Quote
Old 07-15-2019, 07:57 AM   #2
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,032
Thanks: 40
Thanked 3,578 Times in 3,456 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: Condensing a history

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
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 07-15-2019, 08:42 AM   #3
PRTP
Newly Registered User
 
Join Date: May 2014
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
PRTP is on a distinguished road
Re: Condensing a history

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.

PRTP is offline   Reply With Quote
Old 07-15-2019, 08:57 AM   #4
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,032
Thanks: 40
Thanked 3,578 Times in 3,456 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: Condensing a history

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
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 07-15-2019, 09:11 AM   #5
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 3,665
Thanks: 38
Thanked 891 Times in 874 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Condensing a history

Quote:
Originally Posted by PRTP View Post
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...
Attached Images
File Type: png players.PNG (10.8 KB, 84 views)
File Type: png query.PNG (7.5 KB, 81 views)
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 07-15-2019, 09:16 AM   #6
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,032
Thanks: 40
Thanked 3,578 Times in 3,456 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: Condensing a history

what happened to row 2 start date?
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 07-15-2019, 09:20 AM   #7
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 3,665
Thanks: 38
Thanked 891 Times in 874 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Condensing a history

Quote:
Originally Posted by CJ_London View Post
what happened to row 2 start date?
Ah, I guess it needs a little bit of tweak (maybe). Thanks.

__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 07-15-2019, 09:32 AM   #8
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 3,665
Thanks: 38
Thanked 891 Times in 874 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Condensing a history

Quote:
Originally Posted by theDBguy View Post
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...
Attached Images
File Type: png newquery.PNG (7.7 KB, 75 views)
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 07-15-2019, 09:48 AM   #9
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 7,623
Thanks: 59
Thanked 2,440 Times in 2,340 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Condensing a history

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;
__________________
"Never stop learning, because life never stops teaching"
arnelgp is online now   Reply With Quote
Old 07-15-2019, 12:07 PM   #10
PRTP
Newly Registered User
 
Join Date: May 2014
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
PRTP is on a distinguished road
Re: Condensing a history

Thanks everyone especially DBGuy



It does the job great, but I'll spend a bit longer trying to understand why it's working
PRTP is offline   Reply With Quote
Old 07-15-2019, 01:11 PM   #11
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 3,665
Thanks: 38
Thanked 891 Times in 874 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Condensing a history

Quote:
Originally Posted by PRTP View Post
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.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 07-15-2019, 03:12 PM   #12
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 1,688
Thanks: 27
Thanked 510 Times in 483 Posts
MajP will become famous soon enough MajP will become famous soon enough
Re: Condensing a history

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.
MajP is offline   Reply With Quote
Old 07-15-2019, 04:29 PM   #13
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 7,623
Thanks: 59
Thanked 2,440 Times in 2,340 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Condensing a history

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.
__________________
"Never stop learning, because life never stops teaching"
arnelgp is online now   Reply With Quote
Old 07-15-2019, 05:08 PM   #14
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 1,688
Thanks: 27
Thanked 510 Times in 483 Posts
MajP will become famous soon enough MajP will become famous soon enough
Re: Condensing a history

Quote:
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.
MajP is offline   Reply With Quote
Old 07-15-2019, 08:40 PM   #15
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 7,623
Thanks: 59
Thanked 2,440 Times in 2,340 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Condensing a history

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.
Attached Files
File Type: zip combine.zip (18.0 KB, 4 views)

__________________
"Never stop learning, because life never stops teaching"
arnelgp is online now   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Report Condensing StevoC Reports 4 04-22-2008 02:33 AM
Condensing if statements edojan Modules & VBA 2 08-01-2007 10:42 AM
condensing records purepremiumpulp Queries 1 07-26-2006 01:11 PM
Help condensing 10 fields into 3 timp Queries 3 04-23-2005 05:32 PM
On Condensing Code Mile-O Modules & VBA 2 01-21-2003 05:24 AM




All times are GMT -8. The time now is 07:52 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World