Multiple Max/Min Values

vipersmind

it can't be!
Local time
Today, 22:43
Joined
Dec 26, 2002
Messages
82
Hi
I am trying to find multiple occurrences of a value, SizeCode. I would like to show the minFrom_Depth and maxTo_Depth for when each SizeCode occurs. Each SizeCode may appear more than once at different depths.

I have the following tbl

tblSampleSize
HoleID
From_Depth
To_Depth
SizeCode

I would like the outcome to be like:
Code:
HOLEID   	MinOfDEPTH_FROM	   MaxOfDEPTH_TO	   SampleSize
UDD3445	0                     54                   WHOLE
UDD3445      54                    69.01                NS
UDD3445      69.01                 90.96                WHOLE
UDD3445	90.96                 108.6                HALF
UDD3445	108.6                 156.5                QTR

this is the SQL for the query I am using now
Code:
SELECT MAINASSAY.HOLEID, Min(MAINASSAY.DEPTH_FROM) AS MinOfDEPTH_FROM, Max(MAINASSAY.DEPTH_TO) AS MaxOfDEPTH_TO, MAINASSAY.SampleSize
FROM MAINASSAY
GROUP BY MAINASSAY.HOLEID, MAINASSAY.SampleSize
HAVING (((MAINASSAY.HOLEID)=[Forms]![GENERATE LOG]![WHICH HOLE]))
ORDER BY Min(MAINASSAY.DEPTH_FROM);

This the out come I am getting
Code:
HOLEID   	MinOfDEPTH_FROM	   MaxOfDEPTH_TO	   SampleSize
UDD3445	0                     90.96                WHOLE
UDD3445      54                    69.01                NS
UDD3445	90.96                 108.6                HALF
UDD3445	108.6                 156.5                QTR

As you can see the WHOLE From/ To straddles the NS From/To.
This currently only gives me the minFrom_Depth/ maxTo_Depth values for a grouped occurrance of a SizeCode even if there are other codes in between.
hope I'm making sense.............

Thanks
Cress
 
If you want it to say 0-156.5 then just get rid of SampleSize from the query.
 
Hey Pat
I am after the from/to intervals for all of the occurrences of each Samplesize code.

They are spread over the entire table in blocks of each code.
The first 50m (0-50m) might be sampled using WHOLE then the next 25m (50-75m) using HALF then the next 25m (75-100m) using WHOLE again and the last 10m (100-110m) using QTR.

Bearing in mind that the table shows each sample as a 1m interval ie(0-50m , 50-75m etc are composites of 1m samples where the Sample size code is the same)

so it would look like

0-50 WHOLE
50-75 HALF
75-100 WHOLE
100-110 QTR

but my query is grouping all of the WHOLE together and showing it as one block like

0-100 WHOLE
50-75 HALF
100-110 QTR


Hope this is clear.
 
Last edited:
There is nothing in the set of data in your sample that could be used to identify the proper grouping. Why would the two "WHOLE" entries remain separate rather than be merged? if your data sample were like the following, you could group by HOLEID to create the desired groups:

Code:
HOLEID   	MinOfDEPTH_FROM	   MaxOfDEPTH_TO	   SampleSize
UDD3445	0                     54                   WHOLE
UDD3445 54                   69.01               NS
UDD3888 69.01              90.96               WHOLE
UDD3445	90.96             108.6                HALF
UDD3445	108.6             156.5                QTR
 
Sorry my mistake

Pat
It seems that I have been misleading as what my dataset looks like. It's too easy to assume that everyone knows what the hell I'm on about.
Anyway tbl as follows:
Code:
HOLEID	From	To	SampCode
UDD1234	0	1	WHOLE
UDD1234	1	2	WHOLE
UDD1234	2	3	WHOLE
UDD1234	3	4	WHOLE
UDD1234	4	5	WHOLE
UDD1234	5	6	WHOLE
UDD1234	6	7	WHOLE
UDD1234	7	8	HALF
UDD1234	8	9	HALF
UDD1234	9	10	HALF
UDD1234	10	11	WHOLE
UDD1234	11	12	WHOLE
UDD1234	12	13	WHOLE
UDD1234	13	14	WHOLE
UDD1234	14	15	WHOLE
UDD1234	15	16	QTR
UDD1234	16	17	QTR
UDD1234	17	18	QTR
UDD1234	18	19	QTR
UDD1234	19	20	QTR
The query would need to result in
Code:
HOLEID	From	To	SampCode
UDD1234	0	7	WHOLE
UDD1234	7	10	HALF
UDD1234	10	15	WHOLE
UDD1234	15	20	QTR
 

Users who are viewing this thread

Back
Top Bottom