Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 09-17-2019, 11:23 AM   #1
karoe
Newly Registered User
 
Join Date: Sep 2019
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
karoe is on a distinguished road
Group Mulitple Data Ranges by ID and Plan Code

Hello,

I have a file of multiple data ranges per EEID ranging from 1/1/2003 to current. The various stop/start dates is how they appear in our HRIS. I am looking for suggestions of code to group by EEID and plan code.

I have the spreadsheet attached void of any confidential data. It was produced in Access.

Thanks in advance,

karoe
Attached Files
File Type: xlsx tbl10103History.xlsx (123.4 KB, 7 views)

karoe is offline   Reply With Quote
Old 09-17-2019, 11:30 AM   #2
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 5,894
Thanks: 57
Thanked 1,287 Times in 1,268 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Group Mulitple Data Ranges by ID and Plan Code

Hi. Can you mock up for us the final result you're trying to achieve from this sample data? 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 09-17-2019, 12:24 PM   #3
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,568
Thanks: 68
Thanked 2,744 Times in 2,629 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: Group Mulitple Data Ranges by ID and Plan Code

what do you mean by group?

this is grouped:

select plan_code, start_date, stop_date from tblHistory order by plan_code, start_date, stop_date;

this is also group:

select distict plan_code, (selec min(t.start_date) from tblhistory as t where t.plan_code=tblhistory.plan_code) as start_date, (selec max(t.stop_date) from tblhistory as t where t.plan_code=tblhistory.plan_code) as stop_date from tblhistory;

__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 09-17-2019, 12:45 PM   #4
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,571
Thanks: 92
Thanked 1,682 Times in 1,560 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Group Mulitple Data Ranges by ID and Plan Code

The question is not clear regarding the start/stop dates. You define date ranges based on two dates. But you are not clear on the selectivity of those date ranges with respect to the other groups you mentioned.

Do you have a single list of dates that define the crossover periods of the date ranges for a given group?

Or do you have a list of dates that define the crossover periods of the data ranges for ALL groups, one set of dates fits all?

Or do you have a list of dates that define date ranges in some other way?

This answer will govern the most likely approach to establish the data relationships you need for this grouping.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 09-17-2019, 01:59 PM   #5
karoe
Newly Registered User
 
Join Date: Sep 2019
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
karoe is on a distinguished road
Re: Group Mulitple Data Ranges by ID and Plan Code

I would like to see each employee ID and the latest date range (latest stop date) for each plan code. An employee can have more than one plan code. I've attached what I would like the end result to look like.
Attached Files
File Type: xlsx Mock_Up_End_Result.xlsx (14.7 KB, 5 views)
karoe is offline   Reply With Quote
Old 09-17-2019, 02:39 PM   #6
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,423
Thanks: 0
Thanked 568 Times in 564 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Group Mulitple Data Ranges by ID and Plan Code

Rather hard to produce that exact output when data doesn't exist. For instance, there is no employee 1009 in history.

Perhaps you should use SELECT TOP N nested query. Requires a unique record identifier, autonumber field should serve. http://allenbrowne.com/subquery-01.html#TopN

SELECT tbl10103History.*
FROM tbl10103History WHERE ID IN (SELECT TOP 1 ID FROM tbl10103History AS Dupe WHERE Dupe.Employee=tbl10103History.Employee AND Dupe.Plan_Code=tbl10103History.Plan_Code ORDER BY Employee, Plan_Code, Stop_Date DESC);

Unfortunately, the query is slow, which is not uncommon for this type of query.
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by June7; 09-17-2019 at 03:01 PM.
June7 is online now   Reply With Quote
Old 09-17-2019, 03:16 PM   #7
plog
Banishment Pending
 
Join Date: May 2011
Posts: 9,404
Thanks: 11
Thanked 2,282 Times in 2,234 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Group Mulitple Data Ranges by ID and Plan Code

We need 2 sets of related data:

A. Starting data. Include table and field names and enough data to cover all cases.

B. Expected results of data in A. Show what data you hope to end up with when you feed in the data from A.

You have provided A data, but you have not provided B data. Your expected results are not based on the A data you provided. Please provide the actual data you expect when you feed in the data from your first post(tbl10103History.xlsx)

plog is offline   Reply With Quote
Old 09-17-2019, 06:45 PM   #8
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,568
Thanks: 68
Thanked 2,744 Times in 2,629 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
it's almost the same as query #2 in post #3:
Code:
select distict company, 
   employee, 
   plan_code, 
   (select min(t.start_date) from tblhistory as t 
       where t.company=tblhistory.company and t.employee=tblhistory.employee 
        and t.plan_code=tblhistory.plan_code) as start_date, 
   (select max(t.stop_date) from tblhistory as t 
        where t.company=tblhistory.company and t.employee=tblhistory.employee 
        and t.plan_code=tblhistory.plan_code) as stop_date from tblhistory;
** replace tblhistory with the name of your table.

arnelgp is offline   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
Group by rolling date ranges Alc Queries 2 10-20-2016 09:21 AM
Any way to see Access FE query access plan to optimize indexes for ORDER / GROUP BY mdlueck Queries 0 09-12-2012 11:52 AM
Trying to plan out a db. Plan is attached. Advice welcome. Colin@Toyota Theory and practice of database design 1 10-31-2007 12:09 PM
indert data to option group by code 24sharon General 0 02-19-2006 10:00 PM
Group by currency ranges morlan Queries 1 10-20-2003 07:19 AM




All times are GMT -8. The time now is 08:43 PM.


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