non-contiguous numeric data reporting

[fando]

I have a set of non-contiguous data which I would like to query 400000 records and return the ranges of contiguous data as a label.

ie.,
10
11
12
14
15
16

output:
10-12
14-16

 

[dhookom]

Assuming tblTest with field [ie].
Create query "qselStart" with the sql:
SELECT tblTest.ie
FROM tblTest
WHERE (Select Count(*) from tblTest t WHERE t.ie+1 = tblTest.ie)=0;
Then create a query "qselRanges" with sql of:
SELECT qselStart.ie,
(Select Max(ie)
FROM tblTest t where t.ie<
(Select Min(ie)
from qselStart q where q.ie> qselStart.ie)) AS [To]
FROM qselStart;

You can then create a query that concatentates the two fields together.

Duane
MS Access MVP

 

[Golom]

Because Access doesn't support derived tables such as

Select a,b,c From (Select * From tbl Where ...)

You need to do this in two steps. First define a query like this


SELECT A.Num AS First, B.Num AS Second
FROM tbl AS A, tbl AS B
WHERE A.Num <= B.Num
AND NOT EXISTS (Select * From tbl As X Where X.Num = B.Num + 1)
AND NOT EXISTS (Select * From tbl As X Where X.Num = A.Num - 1);

And Save it as &quot;Ranges&quot;

Then define another query based on that one like this

SELECT A.First AS StartRange, MIN(A.Second) AS EndRange, (A.First & &quot;-&quot; & MIN(A.Second)) As [Range]
FROM Ranges AS A
GROUP BY A.First;

 

[fando]

Thank you for your assistance!