Splitting and Suming Overlapping Data?

[TinyRobot]

I'm a total non-programmer whose been charged with using MS Access to manage construction data and I've had to learn SQL querying on the fly. One thing that I've been stumped on for days before finally admitting defeat, is I want to split and sum overlapping data where it exists.

For example, here is what an example table might look like where there are multiple drilled stages for a hole.

HoleID            StageStart       StageEnd      CementInjected/FtLength
DRILLEDHOLE#1         0               20              23.1
DRILLEDHOLE#1         20              40              17.5
DRILLEDHOLE#1         40              60              87.5


DRILLEDHOLE#2         0               20              11.1
DRILLEDHOLE#2         10              30              12.4
DRILLEDHOLE#2         30              60              107.0


DRILLEDHOLE#3         0               15              9.1
DRILLEDHOLE#3         10              25              8.4
DRILLEDHOLE#3         25              60              7.0

In the case of #1, that remains unchanged but I'd want overlapping stages in the #2 and #3 holes to combine. The brute-forced output I'd like to see is:

HoleID            StageStart       StageEnd      CementInjected/FtLength
DRILLEDHOLE#1         0               20              23.1
DRILLEDHOLE#1         20              40             17.5
DRILLEDHOLE#1         40              60             87.5


DRILLEDHOLE#2         0               10              11.1
DRILLEDHOLE#2         10              20              12.4+11.1 = 23.5
DRILLEDHOLE#2         20              30              12.4
DRILLEDHOLE#2         30              60              107.0


DRILLEDHOLE#3         0               10              9.1
DRILLEDHOLE#3         10              15              9.1+8.4 = 17.5
DRILLEDHOLE#3         15              25              8.4
DRILLEDHOLE#3         25              60              7.0

Does anyone know a way to achieve this? Maybe I've been wasting my time because is it even possible to do via queries or am I looking at going to VBA? Thanks!

[June7]

I think VBA and writing records to a 'temp' table is the only way because you want records that don't exist. And it won't be easy because have to compare each sequential pair of records to determine if there is overlap.

Or manually fix the original dataset.

[TinyRobot]

I see. It's not very feasible to edit the original data set since it's used for other billing and record purposes.

I was hoping to avoid VBA since it's another thing to learn but it's good to know that I don't need to pull my hair out trying to do this in SQL. Could you elaborate more on how you might approach it using VBA?

[June7]

It would involve opening a recordset object and setting variables and comparing values and writing records to table with INSERT sql action (or adding records to another recordset object) and all that in a looping structure to cycle through the recordset. If all of that is Greek to you, you have a ways to go.