access query to show min max for overlapping intervals

[jimmcd]

i need an access query to combine the lithologies to only have one dpeth from and to for each interval. should look like this
ID Bore Depth1 Depth2 Lithology
96 DDH_1 0 14.48 OOO
100 DDH_1 14.48 22.56 USE
103 DDH_1 22.56 25.6 WOO
105 DDH_1 25.6 30.18 USE
106 DDH_1 30.18 34.75 UOO

with min max querry the USE will go from 14.8 to 30.18 overlapping with the WOO type

but currently looks like this

ID Bore Depth1 Depth2 Lithology
96 DDH_1 0 3.05 OOO
97 DDH_1 3.05 8.94 UOO
98 DDH_1 8.94 12.34 UOO
99 DDH_1 12.34 14.48 UOO
100 DDH_1 14.48 14.71 USE
101 DDH_1 14.71 20.27 USE
102 DDH_1 20.27 22.56 USE
103 DDH_1 22.56 23.47 WOO
104 DDH_1 23.47 25.6 WOO
105 DDH_1 25.6 30.18 USE
106 DDH_1 30.18 32.61 UOO
107 DDH_1 32.61 34.75 UOO
108 DDH_1 34.75 36.78 USE
109 DDH_1 36.78 39.62 USE
110 DDH_1 39.62 40.69 USE
111 DDH_1 40.69 46.02 USE
112 DDH_1 46.02 51.51 USE
113 DDH_1 51.51 60.66 USE
114 DDH_1 60.66 64.62 USE
115 DDH_1 64.62 67.67 USE
116 DDH_1 67.82 89.05 USE
117 DDH_1 89.05 90.07 VQO
118 DDH_1 90.07 92.66 USE
119 DDH_1 92.66 98.02 USE
120 DDH_1 98.02 98.83 USE
121 DDH_1 98.83 99.82 USE
122 DDH_1 99.82 114.6 UAO
123 DDH_1 114.6 121.23 UAO
124 DDH_1 121.23 130.15 UAO
125 DDH_1 130.15 133.86 USAO
126 DDH_1 133.86 152.53 UAO
127 DDH_1 152.53 154.69 UAO
128 DDH_1 154.69 160.32 UAO
129 DDH_1 160.32 163.37 UAO
130 DDH_1 163.37 192.18 UAO
131 DDH_1 192.18 193.7 UAO
132 DDH_1 193.7 230.12 UAO

thnaks

[TheSmileyCoder]

Its very nice that you provide detailed sample data. However I have no easy way of getting this into access and work on it.

If you export your table as a csv file and attach the csv file here, I would be willing to take a look at it. It should be doable I think.

[patjones]

Basically, you need consecutive records for a lithology to collapse down to one line? So, ID's 108 through 116 would become 108 DDH_1 34.75 89.05 USE?

I pasted the data into a text file then imported into Excel, saved as an .XLS 2003 file - attached here.

Pat

[Mihail]

I don't understand how should be arranged your data ?
Use patjones's xls to arrange data as you need to be.

I am sure that can be done either in Access either in Excel, but I can't understand what you wish to achieve.

[patjones]

I think I understand what you need, and I have a SQL query that gets us part of the way there. I'm going to look at it some more later today.

[patjones]

Hi,

Sorry for the delay, as I've been able to think about this only here and there for a little bit. Maybe you have found a solution, but I want to post what I came up with so that maybe we can get the discussion going again.

The SQL shown below accomplishes what you want, except that it leaves off the beginning and ending ID's (so in your sample data, 96 and 126 are left out). I need to think a little more about modifying it to include the endpoints.

SELECT tC.ID, tC.Bore, tC.Depth1, tD.max_Depth2, tC.Lithology
FROM (SELECT tA.ID, tA.Bore, tA.Depth1, tA.Lithology
      FROM group_lithos AS tA, group_lithos AS tB
      WHERE tA.Lithology <> tB.Lithology AND (tB.ID = tA.ID - 1)) AS tC INNER JOIN (SELECT tA.ID AS lower_ID, MIN(tB.Depth1) AS max_Depth2
                                                                                    FROM group_lithos AS tA, group_lithos AS tB       
                                                                                    WHERE tA.Lithology <> tB.Lithology AND tB.ID > tA.ID
                                                                                    GROUP BY tA.ID) AS tD ON tC.ID = tD.lower_ID;
 

There are two sub-queries here. The first one, denoted by "tC", picks out the lowest ID for each contiguous lithology grouping, while the second one, denoted by "tD", gets the highest Depth2. JOINing them together yields the records that you're looking for.

I have a strong suspicion that there's a more concise solution, which I want to keep looking for. This is an interesting problem.