records between dates

[Guest]

I have a large database of contracts that includes: fields = SSN, Yr, and the
like. Years span from 1999 to 2005. Each contract record includes an
individual's SSN. However, most individuals have had multiple contracts,
some in different years, some in the same year.
I need to find individuals (SSN) by first instance. For example,
individuals that are found in year 2000 but NOT in 1999, even though they
also may be found in 2001 or later. The final result I need is a listing of
records by SSN that are between X and Y years but not before X year.

 

[John Spencer]

To get the list of SSN and Yr you would need a query like the following.

SELECT SSN, Min(Yr) as FirstYr
FROM [Your Contracts Table]
WHERE Yr Between 2001 and 2003
GROUP BY SSN

If you wanted more details then that, you could use a coordinated sub-query.

SELECT * FROM
FROM [Your Contracts Table]
WHERE Yr =
(SELECT Min(yr)
FROM [Your Contracts Table] as Temp
WHERE Temp.Yr Between 2001 and 2003
AND TEMP.SSN = [Your Contracts Table].SSN)