Solved: Criteria: exclude entries from query based on continuity of records

[fboehlandt]

Hi everyone,
I have the following database with two tables named 'Information' and 'Performance'. I would like to run a Union-query to combine the information in the two tables. From the first table I include 'ID' as well as 'Fundname', from the second table I would like to include 'Date', 'FundsManaged', and 'Return'. The primary key for both is 'ID'. The relationship is one-to-many. I have the following SQL-query:

SELECT Information.ID, Information.F1, Performance.Date, Performance.FundsManaged, Performance.Return
FROM Information INNER JOIN Performance ON Information.[ID] = Performance.[ID]
WHERE (((Performance.Date)>#4/1/1998# And (Performance.Date)<#4/1/2008#));

This query works fine. Here comes where I get stuck. I would like to include only time series for the different funds that provide continuous performance records (Field: Performance.Return) for the period betweeen 4/1/1998 and 4/1/2008. Thus, if a given fund has a performance history between, for example, 4/1/2000 and 4/1/2008, the fund should be excluded completely from the query. Can anyone help please?

[OBP]

fboehlandt, sorry I am not very good working directly with SQL statements, I prefer queries in design view.
I take it that you also need continuity, i.e. 1998 - 2001 and 2003 to 2008 should be excluded as well?

[fboehlandt]

thanks for your quick reply. That is correct. The number of return observations between the two dates must be 121 (number of months between 4/1/1998 and 4/1/2008). I figure that I somehow must use Count in my sql statement but if you have an idea how to do this using the query wizard I'd be happy to implement it.

[OBP]

I agree, I think a Pre-query would be best using Group by InformationID and Count the Dates between your 2 dates.
You would then use the Results of that query to Filter your Query by Joining via the InfromationID

[fboehlandt]

SELECT Information.ID
FROM Information INNER JOIN Performance ON Information.[ID] = Performance.[ID]
WHERE (((Performance.Date)>=#4/1/1998# And (Performance.Date)<=#4/1/2008#))
group by Information.ID
having count(*) = 121

SELECT Information.ID, Information.F1, Performance.Date, Performance.FundsManaged, Performance.Return
FROM Performance INNER JOIN (Information INNER JOIN MyQuery on Information.ID = MyQuery.ID)
ON Information.[ID] = Performance.[ID]
WHERE (((Performance.Date)>=#4/1/1998# And (Performance.Date)<=#4/1/2008#))

naming the first query MyQuery and running it first works out fine...

[OBP]

Great. :beerchug:

Can you mark the Thread as Solved please?