Criteria: exclude entries from query based on continuity of records

[fboehlandt]

Hi everyone,
I have a normalized table containing the dates and associated monthly performance of several investment funds. The date range is 07/01/1990 to 06/01/2010. Most funds have a performance track record that is less than the maximum 240 observations. Some investment funds also have gaps in their track record. I would like to create a query that selects funds with at least 60 continuous observations between 07/01/1995 and 06/01/2010 (i.e. cut off the first 60 obs + a minimum track record of 60). Asssuming the table is called Performance and the relevant fields are [ID] and [MM_DD_YYYY], I have come up with the following:

SELECT Performance.*
FROM Performance
WHERE (((Performance.MM_DD_YYYY)>=#7/1/1995# And (Performance.MM_DD_YYYY)<=#6/1/2010#))
group by Performance.ID
having count(*) >= 60;

Sadly, this does not work because it would also include an investment fund, for example, with a history of 12/1/1990 to 12/1/1997. This fund should not be included since the track record of 60 must be between the specified earliest and latest date. Any help in this regard is greatly appreciated.

[mohanvijay]

try this


SELECT Performance.ID
FROM Performance
WHERE (((Performance.MM_DD_YYYY)>=#7/1/1995# And (Performance.MM_DD_YYYY)<=#6/1/2010#))
group by Performance.ID
having count(Performance.ID) >= 60;