Filtering a query

  • Thread starter Thread starter simoNamibia
  • Start date Start date
S

simoNamibia

I have a query of drilling data with fields BoreholeID, From, To and
Rocktype. Bear in mind each borehole can have any amount of records and the
same rocktype can appear more than once. Lets say 50% of the holes have their
first Rocktype as SAND and these have STONE as their second entry. The other
50% the first Rocktype is either STONE or ROCK! How can I filter the data out
so that the result is ONLY the first record of each borehole containing STONE
or ROCK. The fields From and To are Numeric?
 
Well, first question is how does one determine which record is the FIRST
record. Is From or To a way to determine which is the first record?

I will assume that From is a measurement of depth and can be used to determine
which is the first record and that there exists only unique values for each
combination of BoreHoldId with From

SELECT D.BoreholeID, D.From, D.To, D.Rocktype
FROM Drilling as D
WHERE D.From =
(SELECT Min(From)
Drilling as D2
WHERE D2.Rocktype in ("Stone","Rock")
AND D2.BoreHoleID = D.BoreholeID)

The two query method (which might be faster) would be to build a query and
save it that will give you the first From for each borehole that meets your
criteria

SELECT BoreHoleID, Min(From) as FirstFrom
FROM Drilling as D2
WHERE D2.Rocktype in ("Stone","Rock")
GROUP BY BoreHoleID

Now use that query and your table and join the query to the table on
boreholeId fields and on the From and FirstFrom fields.

SELECT D.BoreholeID, D.From, D.To, D.Rocktype
FROM Drilling as D INNER JOIN qFirstStoneRock as Q
ON D.BoreHoleID = Q.BoreHoleID
AND D.From = Q.FirstFrom


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
sorry - the From To is the only way.

eg

BH 1

From 0 To 1 SAND
From 1 To 2 SAND
From 2 To 3 SAND
From 3 To 4 STONE

BH 2

From 0 to 1 SAND
From 1 to 2 STONE
From 2 to 3 ROCK

BH 3

From 0 To 1 STONE


Return should look like:

BH 1
From 3 to 4 STONE
BH 2
From 1 to 2 STONE
BH 3
From 0 To 1 STONE

I'll get stuck into your suggestions,

thanks
 
Back
Top