Pulling top 3 within record selection

[chrisdedobb]

I need to pull a report that gives me the desired results as show below. I first show the data that is in the table, then below that is the desired result.

What I am trying to do is pull every employee's most recent 3 appraisal Effective Dates within the last 4 years where Rating is different than X.

Sorry I don't reall know how to explain this using code because I seem to be having a block right now. Not sure where to start really.

Any help would be greatly appreciated!! Thanks again!

Records in tblAPPRAISALS Table:

Employee | Appraisal Effective | Rating
A | 1/10/2006 | 5
A | 1/10/2005 | 4
A | 1/10/2004 | 5
A | 1/10/2003 | 5
A | 1/10/2002 | 5
B | 1/10/2006 | 5
B | 1/10/2005 | 5
B | 1/10/2004 | X
B | 1/10/2003 | 5
C | 1/10/2006 | 4
C | 1/10/2005 | 5
C | 1/10/2004 | 5

My current SQL:

SELECT DISTINCT TOP 3 Employee, Appr_Eff as [Appr Effective], Rating_Of_Rcd as [Rating]
FROM tblAPPRAISALS
WHERE Appr_Eff >= DateAdd("YYYY", -4, Date()) AND Rating_Of_Rcd <> "X"
Order by 1, 2 DESC

Results from this Query:

Employee | Appraisal Effective | Rating
A | 1/10/2006 | 5
A | 1/10/2005 | 4
A | 1/10/2004 | 5

Desired Results:

Employee | Appraisal Effective | Rating
A | 1/10/2006 | 5
A | 1/10/2005 | 4
A | 1/10/2004 | 5
B | 1/10/2006 | 5
B | 1/10/2005 | 5
B | 1/10/2003 | 5
C | 1/10/2006 | 4
C | 1/10/2005 | 5
C | 1/10/2004 | 5