kg6lfz

I have Access 2000 database. In its table, there is a
field, called Case and field type is number.

Usually its data is in sequence. But sometimes, it skips
one number. I want to list the beginning of records, and
end of sequence.

For example, data is 1, 2, 3, 5, 6, 7, 8, 10, 11.
Report should list:
From 1 to 3
From 5 to 8 and
From 10 to 11.

Do you have any functions that make me do this?
I appreciate your suggestions in advance.

Thank you very much.

nigel rivett

See
http://www.nigelrivett.net/FindGapsInSequence.html

vishal parkar

Hello,

See if following helps.
--sample data
CREATE TABLE #Test
(
IDNo INT UNIQUE
)
INSERT INTO #Test VALUES(9)
INSERT INTO #Test VALUES(10)
INSERT INTO #Test VALUES(6)
INSERT INTO #Test VALUES(5)
INSERT INTO #Test VALUES(1)
INSERT INTO #Test VALUES(2)

-- query to get min number of the gap
select idno from #test where idno in
(SELECT
case when
((select min(idno) from #test where idno > a.idno)
- idno) > 1 then idno else null end
FROM #Test a)

-- query to get max number of the gap
select idno from #test where idno in
(SELECT
case when
(idno - (select max(idno) from #test where idno < a.idno)
) > 1 then idno else null end
FROM #Test a)
order by 1 asc

--
- Vishal