Min/Max ranges

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,
How do you group consecutive ranges within a larger range. I have a range of
sample numbers say G001 to G100 but there are missing samples say G010 and I
want to find the consectutive ranges G001 to G009 and G011 to G100. I'm new
at this so any help much appreciated.
Thanks
Shar
 
Shar:

As your values are text rather than numbers you'll need to use the VAL and
MID functions to return numeric equivalents. This assumes that all begin
with the same letter, in whxcih case the query would go like this:

SELECT T1.MyField As Lower, MIN(T2.MyField) AS Higher
FROM MyRanges AS T1, MyRanges AS T2
WHERE VAL(MID(T1.MyField,2)) <=VAL(MID(T2.MyField,2))
AND NOT EXISTS
(SELECT *
FROM MyRanges AS T3
WHERE VAL(MID(T3.MyField,2)) NOT BETWEEN
VAL(MID(T1.MyField,2)) AND VAL(MID(T2.MyField,2))
AND (VAL(MID(T3.MyField,2)) = VAL(MID(T1.MyField,2)) -1
OR VAL(MID(T3.MyField,2)) = VAL(MID(T2.MyField,2)) + 1))
GROUP BY T1.MyField;

Ken Sheridan
Stafford, England
 
Back
Top