Select Top 2 Records

[joesmithf1]

Hi,
My question 'sounds' easy, but i can't think of a way to do this. Please help!

Ok, i have an 'employee' history table with 200K records. Each employee could have multiple records with different 'effective' dates(please see below for an example of two employees' records). What I want to do is to run a query where it will pick up ONLY the top TWO records of each emplyee. How would I go about doing this?

Thank you.

Joe


Employee History Table:
SSN--------------------EFFV_Date
123-45-6789---------------01/02/2006
123-45-6789---------------12/03/2001
123-45-6789---------------08/22/2000
222-33-4444---------------05/28/2003
222-33-4444---------------07/01/1995
222-33-4444---------------06/30/1990

Query result should be somthing like this:
SSN-----------------------EFFV_date
123-45-6789---------------01/02/2006
123-45-6789---------------12/03/2001
222-33-4444---------------05/28/2003
222-33-4444---------------07/01/1995

 

[pbaldy]

These help?

http://support.microsoft.com/kb/210039/en-us

http://support.microsoft.com/kb/208822/en-us

 

[joesmithf1]

Hi, Thanks! But this didn't help. I followered the steps but the query only return the top N records of the entire table. For example, I specified the top 3 record so all it(the query) did was returned the top 3 records, it did NOT return the top 3 records of EACH INDIVIDUAL in the table. Any other suggestion, anyone?

Thank you!

Joe

 

[MStef]

Hello Joe!

Look at "DemoTop2A2002.mdb" (attachment).
Look at Table1, Query1, Query2, Module1.
Open Query2.
I suggest to make a report on Query2.