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.

 

[plog]

This is sort of a running total query. What I would do is create a query that assigns a month number to every update in Performance, then create a query based on that which looks back into it and determines how many in a row each record represents.

Paste this code into a query and save it naming it 'PerformanceMonths'

SELECT Performance.ID, DateDiff("m","7/1/1995",[MM_DD_YYYY])+1 AS MonthNum
FROM Performance
WHERE (((Performance.MM_DD_YYYY)>=#7/1/1995# And (Performance.MM_DD_YYYY)<=#6/1/2010#))
GROUP BY Performance.ID, DateDiff("m","7/1/1995",[MM_DD_YYYY])+1;

It determines the months each ID has had an update in the time frame you specified (7/1/1995 - 6/1/2010) and then takes that month and gives it a value based on 7/1995 being Month 1.

The below query will produce all the ID numbers that have had 60 months of consecutive updates based on that sub-query:

 SELECT PerformanceMonths.ID
FROM PerformanceMonths
WHERE (((DCount("[ID]","PerformanceMonths","[ID]=" & [ID] & " AND [MonthNum]>=" & ([MonthNum]-59) & " AND [MonthNum]<=" & ([MonthNum])))=60))
GROUP BY PerformanceMonths.ID;

For every record in PerformanceMonths the above query counts how many months an ID has been updated from 59 months ago to the current month of the record. If its 60 you have a winner.

 

[fboehlandt]

Hi,
thanks for your quick response. Your combined query is working very well. Do you have any recommendations to speed up execution? (e.g. splitting the query etc...)

 

[plog]

Anytime you are looking back into a query (like that DCount in the second SQL I posted) its going to take awhile. You might make a table out of the sub-query and then run the main query off it. That would be my only suggestion if the query is taking an hour or so to run.