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:
this is the SQL for the query I am using now
This the out come I am getting
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
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