jbakestull
asked on
Access Query Grouping Dates
This isn't an easy task, but I was wondering if this is would be possible.
Below details is an example of a client service history. I need to somehow group the clients history into service periods. It involves identifying break in consecutive days.
The reason why the client may exit and reenter on same day is because the entry and exit dates contain time stamps. Basically the person left the building to seek medical service and then returned.
Details
Client Provider Id Entry Date Exit Date
1460 Engagement 7/13/2011 7/14/2011
1460 Engagement 7/14/2011 7/21/2011
1460 Engagement 7/21/2011 7/26/2011
1460 Engagement 7/26/2011 8/3/2011
1460 Engagement 8/8/2011 8/9/2011
1460 Engagement 8/9/2011 8/12/2011
1460 Engagement 8/12/2011 8/19/2011
1460 Engagement 10/7/2011 10/8/2011
1460 Engagement 10/8/2011 10/10/2011
1460 Engagement 10/11/2011 10/12/2011
1460 Engagement 10/13/2011 10/17/2011
1460 Engagement 10/17/2011 10/19/2011
1460 Engagement 10/19/2011 10/20/2011
1460 Engagement 10/20/2011 10/21/2011
1460 Engagement 10/21/2011 10/21/2011
1460 Engagement 10/22/2011 10/23/2011
1460 Engagement 10/23/2011 10/24/2011
1460 Engagement 10/24/2011 10/26/2011
1460 Engagement 10/26/2011 10/27/2011
1460 Engagement 10/27/2011 10/28/2011
1460 Engagement 10/28/2011 10/31/2011
1460 Engagement 11/9/2011 11/10/2011
1460 Engagement 12/14/2011 12/15/2011
1460 Engagement 12/15/2011 12/16/2011
Results
Client Provider Id Entry Date Exit Date
1460 Engagement 7/13/2011 8/03/2011
1460 Engagement 8/08/2011 8/19/2011
1460 Engagement 10/07/2011 10/31/2011
1460 Engagement 11/09/2011 11/10/2011
1460 Engagement 12/14/2011 12/16/2011
Below details is an example of a client service history. I need to somehow group the clients history into service periods. It involves identifying break in consecutive days.
The reason why the client may exit and reenter on same day is because the entry and exit dates contain time stamps. Basically the person left the building to seek medical service and then returned.
Details
Client Provider Id Entry Date Exit Date
1460 Engagement 7/13/2011 7/14/2011
1460 Engagement 7/14/2011 7/21/2011
1460 Engagement 7/21/2011 7/26/2011
1460 Engagement 7/26/2011 8/3/2011
1460 Engagement 8/8/2011 8/9/2011
1460 Engagement 8/9/2011 8/12/2011
1460 Engagement 8/12/2011 8/19/2011
1460 Engagement 10/7/2011 10/8/2011
1460 Engagement 10/8/2011 10/10/2011
1460 Engagement 10/11/2011 10/12/2011
1460 Engagement 10/13/2011 10/17/2011
1460 Engagement 10/17/2011 10/19/2011
1460 Engagement 10/19/2011 10/20/2011
1460 Engagement 10/20/2011 10/21/2011
1460 Engagement 10/21/2011 10/21/2011
1460 Engagement 10/22/2011 10/23/2011
1460 Engagement 10/23/2011 10/24/2011
1460 Engagement 10/24/2011 10/26/2011
1460 Engagement 10/26/2011 10/27/2011
1460 Engagement 10/27/2011 10/28/2011
1460 Engagement 10/28/2011 10/31/2011
1460 Engagement 11/9/2011 11/10/2011
1460 Engagement 12/14/2011 12/15/2011
1460 Engagement 12/15/2011 12/16/2011
Results
Client Provider Id Entry Date Exit Date
1460 Engagement 7/13/2011 8/03/2011
1460 Engagement 8/08/2011 8/19/2011
1460 Engagement 10/07/2011 10/31/2011
1460 Engagement 11/09/2011 11/10/2011
1460 Engagement 12/14/2011 12/16/2011
You're not going to do this in a query. It will require a VBA procedure. Is that an acceptable approach?
Are you attempting to do this for a specific (short) time period for a single client, or for several years for multiple clients?
ASKER
Sorry, went out to lunch with wife. We don't get to do that very often.
Thanks for the responses, VBA is fine, for multiple clients over several years.
Thanks for the responses, VBA is fine, for multiple clients over several years.
As I assume your test data is representative - where the break in consecutive days happens between months - the query will work for multiple clients over several years.
/gustav
/gustav
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
peter57r, I'm amazed. I will play with this over the weekend and get back with you. Thank you. You answered my question and did an awesome job. 500 points is really not enough.
Select
Client,
[Provider Id],
Min([Entry Date]) As EntryDate,
Max([Exit Date]) As ExitDate
From
tblYourTable
Group By
Client,
[Provider Id],
Year([Entry Date]),
Month([Entry Date]);
/gustav