Assigning attribute to overlapping intervals

[LouiseSemaj]

I have rock type data for a drillhole in the form of from and to depth interval down the drillhole. I also have sample chemistry data that has different from-to intervals down the hole to the rock types. I want to assign the rock type (majority code) to each of the chemistry samples. Note that intervals may be overlapping and variable in length. eg
Rock Types
15-20 Conglom
20-22 Sandstone
22-25 Shale
25-32 Coal

Chemistry Sample intervals
A 16-20
B 20-25
C 25-30

I am wanting a query / code to be able to assign rock types to samples A, B and C as follows

• A Conglom
• B Shale
• C Coal

I am sure I am not the first person to be stumped with this question - Please point me to another thread if applicable. I have posted under queries, but also point me if this is more applicable to another forum area. Many thanks for any interest in my query, Please let me know what this is called in database speak so I may be able to search other areas also. Kind regards

[orange]

You have shown us some numbers, but what does the scale mean in plain English.
I don't understand a designed range approach where the upper bound of 1 is equal to the lower bound of another.

eg
Rock Types
15-20 Conglom
20-22 Sandstone

What does it mean to have a sample of Rock type 20??
Is it Conglom or Sandstone?? Why?

There typical approach to ranges or categories is that something is distinctly assigned to 1 range or 1 class.

[LouiseSemaj]

Many thanks for your interest in my question. The numbers given are in metres down a drillhole. I was obviously not explicit in my original question. So if the rock type down the drillhole between 15 m and 20m is conglomerate, a chemistry sample from 16-20m is mainly Conglomerate. Between 20 and 25m I have 2m of sandstone followed by 3m of shale. But have a single combined 5m chemistry sample from 20-25m. So the majority rock code of this chemistry sample will be shale. I hope this helps explain the issue a little further.
Many thanks and kind regards

[orange]

I have looked at you requirement and came up with the attached as one possible solution.
See attached jpg.

The result of the query sql shown is

StrataType
StrataStart
StrataEnd

Sandstone
20
22

Shale
22
25

[LouiseSemaj]

Many thanks for your thoughts here... I don't think this is quite it yet.. The result of the code above looks just like the tblStrataRockType table, but I might be missing something. However I think your setting out the tables and nomenclature will help me communicate the question at hand...
The results I would like to see would be ChemSampleID, FromDepth, ToDepth, StrataType - so the combination of the data tables - (see image)

[orange]

Yes, sort of. There are 2 tables required based on m y reading of your original opportunity/issue.
1: A table identifying the various strata and start/end of each.
2: A ChemSample table identifying the depth/extent of the sample's origin.

The 2 tables in a query will identify the rocktype in the various strata through which the chemical sample passes.

My query dealt with chemsample = 2. However, it should serve as a base/template for other queries.
You should be able to determine all strata within the ChemSample depth/extent.

Good luck

[LouiseSemaj]

Many thanks for your ideas orange, I will give them a go, Kind regards