Find latest change dates based on another field in the table where duplicate value exists

[bd1048]

Hi

I need help to find out latest Effective_Dates form below list (sample from a large table tblPayRateHist). So far I am able to get top 3 values from the list but I end up with duplicate Effective_Date for 01/01/2015 because There are two entries for this date with Different Change_Date.

I tried this which gives me duplicate Effective_date:

"SELECT tblPayRateHist.EmployeeID, tblPayRateHist.PayRateID, tblPayRateHist.CHANGE_DATE, tblPayRateHist.EFFECTIVE_DATE
FROM tblPayRateHist
GROUP BY tblPayRateHist.EmployeeID, tblPayRateHist.PayRateID, tblPayRateHist.CHANGE_DATE, tblPayRateHist.EFFECTIVE_DATE
HAVING (((tblPayRateHist.EFFECTIVE_DATE) In (SELECT TOP 3 EFFECTIVE_DATE
FROM [tblPayRateHist] AS T1
WHERE T1.EmployeeID=[tblPayRateHist].[EmployeeID])))
ORDER BY tblPayRateHist.EmployeeID, tblPayRateHist.EFFECTIVE_DATE;"

PayRateHistID........	EmployeeID...........	PayRateID...........	CHANGE_DATE................	EFFECTIVE_DATE
51798	21	110	31/12/2007	14/12/2007
51795	21	93	27/11/2008	1/01/2008
51799	21	111	27/05/2008	16/05/2008
51800	21	111	3/10/2008	19/09/2008
51796	21	93	13/01/2009	1/01/2009
51797	21	94	25/08/2009	14/08/2009
51785	21	68	8/09/2009	28/08/2009
51786	21	68	21/05/2010	1/01/2010
51787	21	68	15/01/2011	1/01/2011
51788	21	70	11/01/2012	1/01/2011
51790	21	71	5/09/2012	1/01/2011
51789	21	70	24/02/2011	14/02/2011
51791	21	71	2/12/2013	1/01/2012
51792	21	71	2/01/2014	1/01/2014
51793	21	71	25/03/2014	20/03/2014
51794	21	71	15/01/2015	1/01/2015
51784	21	24	15/04/2015	1/01/2015

What I am trying to achieve is to Get the Top n Effective_Dates and the corresponding PayRateID for Each Employee until a cut-off date has reached.

For Example, If the cut-off Date is 01/01/2014 then I should have below items. Same Effective_Date with later Change_Date takes priority over one with earlier Change_Date And I should not have Same Effective_Date more then once.

PayRateHistID...........	EmployeeID.........	PayRateID..........	CHANGE_DATE.................	EFFECTIVE_DATE
51792	21	71	2/01/2014	1/01/2014
51793	21	71	25/03/2014	20/03/2014
51784	21	24	15/04/2015	1/01/2015

Your help is much appreciated. Please note I am new to database and trying to learn SQL.

[NTC]

It is a 2 step design. First you must create an Aggregate query (Sigma symbol in ribbon that looks soft of like E) to establish the correct record set. You would group on Employee ID and group on Effective Date and set the Change Date field to Max. A 3 field query....save it with a name like MaxChangeDates

Then use MaxChangeDates in a new query with your table joining on both Employee ID and the Change Date fields - and include all fields of the table....don't include any fields from the MaxChangeDates query - its role is to identify/join to the records that you need.

[bd1048]

Hi NTC,

Thank you very much for your help. It worked for removing duplicates. Do you have any Idea how can I restrict the number of records for each employees to the desired value? for example. say if my reporting period is 01/02/2014 then from the above record list I need to select only to 01/01/2014 ( i.e. only 01/01/2014; 20/03/14;01/01/2015) and ignore rest of the values prior to 01/01/2014.

I can use Top 3 or so function but thinking if there is a way to make it dynamic since I will have situation where if i only have just the top one value will be sufficient.

because I created another query using this query as suggested in below post.
https://www.accessforums.net/queries/...ery-53657.html
(SELECT Max(EmpSalChangeDate) FROM SalToEmp_tbl AS T WHERE EmpID=SalToEmp_tbl.EmpID AND EmpSalChangeDate<=WorkDate)

but when I use select max function in the criteria as sub query this query runs very very slow (some time doesn't finish even) though without sub query main query returns thousands of records very quickly. (I believe sub query is run for every record? which slows the process) thats why I am thinking if I can minimise records for effective_date to bare minimum then this should reduce the combination and query should run faster? Please correct I am wrong.

I am open to all suggestions. thanks