Find gaps between activities (1 Viewer)

[John S]

I've got a table 'Activity' with around 9000 records built up over the past 18 months. Each record has three fields: ID (autonumber), Start (datetime) and End (datetime). An activity can start and end at any time (denoted Start and End), and be of any duration. Generally there are several activities underway, but occasionally nothing's happening.

What I'm struggling to do is create a query which returns the Start and End date/time of each 'gap' - i.e. all the periods when there was no activity.

I assume this requires 2 instances of the table so that the end of each activity can be compared with all the other Start / Ends to see if it falls in a gap, but nothing works so far, so grateful for any help.

 

[ajetrumpet]

John,

I'm not sure if you could do this in a query type setting, but you may be able to do it by way of Visual Basic. It would certainly be a challenge. Do you know any of the language?

Do you think there is any other way you could go about it? If you did it with code, I'm sure you would have to use forms too.

I am thinking that if you did it with the query object in Access, it would take you at least 3 stacked queries to get it done...

 

[John S]

John,

...you may be able to do it by way of Visual Basic. It would certainly be a challenge. Do you know any of the language?
...
Do you think there is any other way you could go about it? ...

Thanks - I've a very sketchy grasp of VB in Access - if it came to that I'd be more likely to export the data and try to cobble something together in C. An unsatifsying workaround rather than a neat solution.

It's a frustrating problem, as it's easy to represent graphically (like a series of timelines in MS Project or similar) but it's proving so difficult to solve!

John