Query based on two sets of depth intervals

[Casa]

I have two tables (this all has to do with drilling). The first table
consists of a drill hole label, from depth, to depth, and geology (i.e. what
rock is present at that interval). My second table has a drill hole label,
sample number, from depth, to depth, and nickel content. I want to be able
to query out what geology is present (from table 1) for each sample (table 2)
interval. The geology intervals do not necessarily match the sample
intervals.

I'm unsure how to code this. Is there anyone out there who could give me
some pointers? Anything I've tried so far has given me bad results.

Casa

 

[Jerry Whittle]

Please post some sample data from each table with the proper table and field
names.

Then show an example of how you would like to see the data returned from the
query.

 

[Casa]

Here is some example data:

Geology table:

DH Label From(metres) To(metres) Geology
1 0 1 UM
1 1 5 GB
1 5 8.5 SH
1 8.5 11.2 MV

Samples table:

DH Label Sample# From(metres) To(metres) Nickel(ppm)
1 100 1 2 3000
1 101 2 3 2200
1 102 3 4 1600
1 103 4 5 2000
1 104 5 6 560
1 105 6 7 870
1 106 7 8 1500
1 107 8 9 2400
1 108 9 10 3080

I'd like to see:

DH Label Sample# From(metres) To(metres) Nickel(ppm) Geology
1 100 1 2 3000
GB
1 101 2 3 2200
GB
1 102 3 4 1600
GB
1 103 4 5 2000
GB
1 104 5 6 560
SH
1 105 6 7 870
SH
1 106 7 8 1500
SH
1 107 8 9 2400
SH,MV
1 108 9 10 3080
MV

So all I'd like to do is join the geology column (from the geology table) to
the samples table. Is this possible? If a sample spans two different
geological units, is it possible to have Access list both units (as presented
in my example sample interval 8-9 metres)?

Any help would be appreciated.

Casa

 

[Dale Fye]

Will your sample intervals always be in 1m increments, could you have:

DH Label Sample# From(metres) To(metres) Nickel(ppm)
1 100 1 2 3000
1 101 2 3 2200
1 102 3 6 1600
1 103 6 10 2000

If they will always be in 1m increments, this should work:

SELECT S.[DH Label], S.[Sample#], S.[From(meters)],
S.[To(meters)], S.[Nickel(ppm)], G.Geology
FROM Samples S, Geology G
WHERE S.DH = G.DH
AND S.[From(meters)] >= BETWEEN G.[From(meters)]
AND S.[From(meters)] <= G.[To(meters)]
AND S.[To(meters)] >= G.[From(meters)]
AND S.[To(meters)] <= G.[To(meters)]

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[Jerry Whittle]

This query is close but no cigar as it only returns one Geology:

SELECT Samples.[DH Label],
Samples.[Sample#],
Samples.[From(metres)],
Samples.[To(metres)],
Samples.[Nickel(ppm)],
(Select Top 1 Geology.Geology
FROM Geology
WHERE Geology.[DH Label] = Samples.[DH Label]
AND samples.[From(metres)] Between Geology.[From(metres)]
And Geology.[To(metres)]) as Geologies
FROM Samples
GROUP BY Samples.[DH Label],
Samples.[Sample#],
Samples.[From(metres)],
Samples.[To(metres)],
Samples.[Nickel(ppm)] ;