Building a Query to relate intervals in one table which occur within a larger interval in another

[keillor]

Hi all,

First post here, I apologize if this has been covered before - I struggle to explain what I want to do and consequently have had a hard time searching for solutions.

I'm a geological engineer working on a new geological database for my project. In my database I have several tables, including the following two:

Sample
- This table includes fields detailing FROM and TO downhole coordinates from a drillhole;
- It also has sample # fields, which are linked to an assay table showing data for those particular samples.
Geology
- This table also includes fields detailing FROM and TO downhole coordinates from a drillhole;
- Other fields include lithology and other geological descriptors.

For a given lithology there are often many samples. For instance, there may be "Quartzite" logged and described in my Geology table for the interval 3.5 m to 40.69 m. Often the entire interval is sampled on roughly 3 m sample spacing. For instance sample 10001 at 3.5 - 9 m, 10002 at 9 - 6 m...1000x at 36 - 39 m, and 1000y at 39 - 40.69 m.

I am trying to build a query that will populate "lithology" values for each sample interval. Ie. Look for something like Geology.FROM <= Sample.FROM & Geology.TO >= Sample.TO and take the lithology value for that record.

I have a few ideas how I would accomplish this but they all seem, to put it mildly, brutal. I'm sure there are more elegant solutions.

Please let me know if there's a simple way to do this, and by all means let me know if I can clarify my goals/attach sample data. Furthermore, my access skills are limited. I am a fast learner but please, if you can, point me in the right direction with respect to execution.

Cheers, have a great day,
Cam

 

[PHV]

What have you tried so far and where in your code are you stuck ?
anyway, here a starting point:
WHERE Geology.FROM <= Sample.TO AND Geology.TO >= Sample.FROM

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[BigRed1212]

I'm with PHV, this isn't clear.

You want a query that shows the lithology for each sample, and tbl.sample and tbl.geology are related by FROM and TO coordinates?

Explain why:

select lithology
from geology inner join sample on geology.from= sample.from and geology.to= sample.to
order by geology.from

doesn't work. (I assume it doesn't.)