新手跪求前辈们帮帮忙TAT 关于用sql代码将数据段分组并筛选出符合每个数据段的数据

[cMMMMMoira]

大家好！情况是这样的：
表格（第一张大图）有ID, MP__ID, Tiefe_m, TempWert_oC 和ELWert_mScm 五列，分别是表tblGwMeasurement 的主键列，测量记录编号，深度，温度和电导率。现在想通过在access中编写一个sql代码，将深度分段（0~5m,5~10m...145~150m), 并在每一个深度段下面筛选出对应的温度值和电导率值（第二张小图）。这是最好的一步直达的设想，但是我没有能够编写出来，所以想请论坛里的大神们帮帮忙。

如果这个难以实现的话，是否有办法一次性通过类似iif()或者switch()函数在原表tblGwMeasurement的基础上再加一列DepthInterval，对每一个深度添加一个深度段？然后再通过合并表格，筛选出每个深度段对应的温度值们或者电导率们。下面是我自己写的代码，但是由于iff()和switch()都只能写十几个，而我有29个条件。所以我得分成三次做成三个表。但是我觉得，这样肯定会被老师给砍死。
SELECT tblGwMeasurement.ID, tblGwMeasurement.MP_ID, tblGwMeasurement.Tiefe_m, tblGwMeasurement.TempWert_oC, tblGwMeasurement.ELWert_mScm,
IIf(0<=Tiefe_m And Tiefe_m<5,'0-5muGOK',
IIf(5<=Tiefe_m And Tiefe_m<10,'5-10muGOK',
IIf(10<=Tiefe_m And Tiefe_m<15,'10-15muGOK',
IIf(15<=Tiefe_m And Tiefe_m<20,'15-20muGOK',
IIf(20<=Tiefe_m And Tiefe_m<25,'20-25muGOK',
IIf(25<=Tiefe_m And Tiefe_m<30,'25-30muGOK',
IIf(30<=Tiefe_m And Tiefe_m<35,'30-35muGOK',
IIf(35<=Tiefe_m And Tiefe_m<40,'35-40muGOK',
IIf(40<=Tiefe_m And Tiefe_m<45,'40-45muGOK',
IIf(45<=Tiefe_m And Tiefe_m<50,'45-50muGOK',
IIf(50<=Tiefe_m And Tiefe_m<55,'50-55muGOK',
IIf(55<=Tiefe_m And Tiefe_m<60,'55-60muGOK',
IIf(60<=Tiefe_m And Tiefe_m<65,'60-65muGOK',Null)))))))))))))
AS DepthInterval INTO tblDepthInterval0_65
FROM tblGwMeasurement;


SELECT tblGwMeasurement.ID, tblGwMeasurement.MP_ID, tblGwMeasurement.Tiefe_m, tblGwMeasurement.TempWert_oC, tblGwMeasurement.ELWert_mScm,
IIf(65<=Tiefe_m And Tiefe_m<70,"65-70muGOK",
IIf(70<=Tiefe_m And Tiefe_m<75,"70-75muGOK",
IIf(75<=Tiefe_m And Tiefe_m<80,"75-80muGOK",
IIf(80<=Tiefe_m And Tiefe_m<85,"80-85muGOK",
IIf(85<=Tiefe_m And Tiefe_m<90,"85-90muGOK",
IIf(90<=Tiefe_m And Tiefe_m<95,"90-95muGOK"，
IIf(95<=Tiefe_m And Tiefe_m<100,"95-100muGOK",
IIf(100<=Tiefe_m And Tiefe_m<105,"100-105muGOK",
IIf(105<=Tiefe_m And Tiefe_m<110,"110-115muGOK",
IIf(110<=Tiefe_m And Tiefe_m<115,"110-115muGOK",
IIf(115<=Tiefe_m And Tiefe_m<120,"115-120muGOK",
IIf(120<=Tiefe_m And Tiefe_m<125,"120-125muGOK",
IIf(125<=Tiefe_m And Tiefe_m<130,"125-130muGOK",Null)))))))))))))
AS DepthInterval INTO tblDepthInterval65_130
FROM tblGwMeasurement;

SELECT tblGwMeasurement.ID, tblGwMeasurement.MP_ID, tblGwMeasurement.Tiefe_m, tblGwMeasurement.TempWert_oC, tblGwMeasurement.ELWert_mScm,
IIf(130<=Tiefe_m And Tiefe_m<135,"130-135muGOK",
IIf(135<=Tiefe_m And Tiefe_m<140,"135-140muGOK",
IIf(140<=Tiefe_m And Tiefe_m<145,"140-145muGOK",Null)))
AS DepthInterval INTO tblDepthInterval130_150
FROM tblGwMeasurement;

实在是不好意思， 这个问题看起来有点烦杂。 再次谢过大家了！

[zYiGJN]

你这个分院是由规律的嘛

[zYiGJN]

SELECT * , CONVERT(VARCHAR,TEAM*5) + '~' +  CONVERT(VARCHAR,TEAM*5+5) FROM (
	SELECT * , CAST (FLOOR(ISNULL(Tiefe_m,0)/5) AS VARCHAR) TEAM FROM tblGwMeasurement
) Z 

ID MP__ID Tiefe_m TempWert_oC ELWert_mScm TEAM
------------------------ ------------------------ --------------------------------- --------------------------------- --------------------- ------------
1 MP1 2.23 10 .513 0 0~5
2 MP2 4.23 10 .513 0 0~5
3 MP3 6.23 10 .513 1 5~10
4 MP4 8.23 10 .513 1 5~10
5 MP5 10.23 10 .513 2 10~15
6 MP6 12.23 10 .513 2 10~15

[zYiGJN]

你先看有没有规律，如果有规律上面的就可以了