Achieving a loop effect in query

[phual]

I've got a problem for which I'm far from convised there is a solution. Consider this scenario:

You have a client for whom you can have any number of activities / events / whatever, each with an associated start and end date. I need to return all activities that 'open' a new episode of participation with the client excluding those that do not.

Example:

Activity 1: 10/03/10 to 12/05/10 - include, because first contact and new episode
Activity 2: 10/05/10 to 23/05/10 - exclude, because it overlaps with (and ehce extends) existing episode
Activity 3: 01/07/10 to 30/07/10 - include because previous expisode was closed... this is a new episode

This wouldn't be too hard to do in VBA, but I'm wondering if it is possible to do it in pure SQL, ideally avoiding a LOOP (which I'm not even sure MS Access supports). Altering the way in which the data is captured is not an option - I can only work with the data as I have it.

Thoughts?

Stuart

 

[JANR]

perhaps this might help: http://baldyweb.com/OverLap.htm

JR

 

[phual]

Thanks for the response, but unfortunately this does not address my problem. The solution you link to only checks an individual record with external parameters - my problem requires comparisons between records. The solution as linked would return all three of my examples activities, when I need it to exclude the second.

Any other ideas?

Stuart