Criteria: exclude entry 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?

 

[namliam]

Something like so?

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#))
and Information.id not in (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 id
having count(*) > 1)

 

[fboehlandt]

If I change your last expression to:
group by Information.ID
having count(*) > 1)
the query runs but is extremely slow. So far, I could not produce any results. Is there a shortcut?

 

[namliam]

slow but steady... no shortcut's sorry...

You could make the subquery:
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(*) > 1

As a seperate query and do an outer join to it, which should be a little faster....

 

[fboehlandt]

The problem is that the query just keeps running. Testing other queries, access usually displays a loading bar that shows me the progress in calculating the query. I have run many intricate queries on the same database that were resolved within a minute. My computer is a fairly new high-end model and the database is not exceptionally large (about 7000 funds with 50 -150 return observations each). In my opinion, my intended query should be quite comom and not difficult to figure out.

I'm still not sure what you do when you run this part:
group by Information.ID
having count(*) > 1

I would like to exclude any fund (as uniquely identified by its ID) that has either more than 0 empty return observations for the period between the specific dates or, alternatively, has return observations exactly equal to 121 (121 months between 4/1/1998 and 4/1/2008). I'm not sure what the inequality above does? Maybe I can rephrase my problem if you can briefly explain the logic behind your query. I'm worried I might not have specified my problem correctly. From a logical viewpoint, I would go about the problem as follows:
1. count the number of return observations in the performance table for each unique ID
2. exclude all funds that have # of return observations <> 121
Is that what you are doing here? Your help is greatly appreciated

 

[fboehlandt]

ps the query has been running for 20 minutes now...no results

 

[namliam]

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(*) > 1

This query selects all the IDs that have more than 1 record, or more than one Performance record, the count(*) does the counting and the Group by tells it how to count.

You can try making a query only with this information.

But since you want something else than those that have more than one, but want to exclude all that are <> 121 just change that part. Just this query should be pretty fast...
The NOT IN part is what is DOG slow... changing that to an outer join would help...

So try running this query and see how that runs.

 

[fboehlandt]

The following returns a syntax error and I'm not quite sure why:

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#))
RIGHT JOIN
(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)

The part following RIGHT JOIN works fine on its own and is farily quick.

 

[namliam]

You are missing the ON part and the syntax is just wrong...

Try creating a new query for:
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

Save this query as qryNamliam

now run this:
SELECT Information.ID, Information.F1, Performance.Date, Performance.FundsManaged, Performance.Return
FROM Information INNER JOIN Performance ON Information.[ID] = Performance.[ID]
inner join qryNamliam on information.ID = qryNamliam.ID
WHERE (((Performance.Date)>=#4/1/1998# And (Performance.Date)<=#4/1/2008#))


That should return all the IDs that have performance returns exactly 121 times.

 

[fboehlandt]

Sorry, my first attempt was complete nonsense...not very SQL proficient. The following for the second query seems to do the trick and is also very fast:

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

The modifications were necessary because a syntax error message was displayed. Thank you for your great support

 

[namliam]

well atleast it gave you the right idea, even if my code contained an error

Glad you got it working, of course you can change the query name to what ever you want