Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old June 14th, 2007, 05:57 PM
Friend of Wrox
 
Join Date: Jul 2003
Posts: 128
Thanks: 0
Thanked 0 Times in 0 Posts
Default Select the top 3 record of each employee

Hi,
My question 'sounds' easy, but i can't think of a way to do this. Please help! NOTE: I am NOT asking to get only the top 3 records of the entire table, I am asking to get the top 3 record of [u]EACH</u> employee from the table.

Ok, i have an 'employee' history table with 200,000 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 [u]each emplyee</u>. 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


 
Old June 16th, 2007, 01:16 AM
Authorized User
 
Join Date: May 2006
Posts: 47
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to Ashfaque
Default

How about 2 records for a particular employee in same date? I mean, is there any possibility of 2 records in one date? If so, you need first to change your date format that include times (hh:mm:ss).






Similar Threads
Thread Thread Starter Forum Replies Last Post
SELECT TOP n rgerald SQL Server 2000 3 May 12th, 2006 04:03 PM
SELECT TOP n NOT SELECTING TOP n! ibi SQL Language 8 March 30th, 2005 08:08 PM
SELECT TOP FROM HAVING khatfield29 SQL Language 1 August 23rd, 2004 02:41 PM
Select employee name spelling mateenmohd SQL Server 2000 21 June 7th, 2004 02:23 AM
SELECT TOP !!!! Jane SQL Language 2 October 17th, 2003 11:22 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.