Desired outcome. To return the the value (position Description) that's attached to the max date in a table.

 

My report is grouped by Employee ID (from EMPLOYEE table), then by Position level (in PAEMPPOS table, which has position # and it's respective position level). I have another table (PAPOSITION) that stores the description of each position. This table stores historical position descriptions and they have effective and end dates. My goal is to display the position description for the max effective date. See example of position description and eff/end date below:

Query2

POSITION	DESCRIPTION	EFFECT_DATE	END_DATE
4858	7441F03030 6413OAKST 1807N	1/1/1950	3/17/2013
4858	7441F03030 6413OAKST 5131N	3/18/2013	4/28/2013
4858	7441F03030 6413OAKST 5641N	4/29/2013	4/29/2013
4858	7441F03030 5366386ST 5641N	4/30/2013	1/1/1700

Each employee is tied to a position#(one to many relationship). I need to pull in the MAX effective date (4/30/2013) for every record. I tried creating a Group on Effective date, ascending, but that isn't working right.

 

If you need more info from me I'll be happy to provide. Thanks!

effective date<>max(effectivedate,position)

 

Note this does not remove the other rows, they are just suppressed (hidden).