Test for and group consecutive numbered records

  • Thread starter Thread starter John
  • Start date Start date
J

John

I have created a database that as a final result gives me
a table of data (tblPRI_Match) as below:-

PRI Min Max
62 0 3
63 0 4
64 0 3
67 0 3
69 0 4
70 0 5
71 1 3
....

The field of interest is the PRI field and as you can see
this has some entries that run consecutively i.e 62-64,
67, 69-71 etc... What I need to be able to do is give a
result on either a Report or as a straight output to
printer, that gives the group of PRI's (e.g. 62-64 etc)
and then checks the Min and Max fields for that group and
only outputs the min and max values of the Min and Max's
for that group of PRI's (if that makes sense!)

Any Help would be much appreciated
 
Salut,

Untested.



SELECT pri+LAST(offset) AS limit , LAST(offset) AS offs
FROM (
SELECT pri, 0 As offset FROM myTable
UNION ALL
SELECT pri+1, -1 FROM myTable
) As a
GROUP BY pri
HAVING COUNT(*) =1



will supply the limits:
62 0
64 -1
67 0
67 -1
69 0
71 -1


Save it as q1.


It is then a matter to rebuild the "pairs"


62 64
67 67
69 71

The following query should do:


SELECT a.limit, MIN( b.limit ) As maxmit
FROM q1 As a INNER JOIN q1 As b
ON b.limit>= a.limit
WHERE a.offs =0
AND b.offs = -1
GROUP BY a.limit


say, it is q2.



The final and easiest part:


SELECT a.limit, a.maxmit, MIN(b.TheMin), MAX(b.TheMax)

FROM q2 As a INNER JOIN myTable As b
ON b.pri BETWEEN a.limit AND a.maxmit

GROUP BY a.limit, a.maxmit




Note that MIN and MAX are reserved keyword, preferable not to use them for
fields name.




Hoping it may help,
Vanderghast, Access MVP
 
Back
Top