SELECT Query finding contiguous ranges

[kevpete]

Working on fixing an Access Database

Stores and reports used/unused phone extensions

Table looks like this:

TblExtensions

No | Switch | Use | BroadUse
290 | ABC | DEF GHI | JKL/MNO
291 | ABC | DEF GHI | JKL/MNO
292 | ABC | DEF GHI | JKL/MNO
293 | ABC | DEF GHI | JKL/MNO
294 | ABC | DEF GHI | JKL/MNO
295 | ABC | DEF GHI | JKL/MNO
296 | ABC | DEF GHI | JKL/MNO
298 | ABC | DEF GHI | JKL/MNO
299 | ABC | DEF GHI | JKL/MNO
300 | OPEN | OPEN | OPEN
301 | OPEN | OPEN | OPEN
302 | OPEN | OPEN | OPEN
303 | OPEN | OPEN | OPEN
304 | OPEN | OPEN | OPEN
305 | OPEN | OPEN | OPEN
306 | ABC | DEF GHI | JKL/MNO
307 | ABC | DEF GHI | JKL/MNO
308 | ABC | DEF GHI | JKL/MNO

I need to create a query that outputs data in a table or report like this:

NoRange | Switch |Use | BroadUse

290-299 | ABC | DEF GHI | JKL/MNO
300-305 | OPEN | OPEN | OPEN
306-308 | ABC | DEF GHI | JKL/MNO

Simple Min/Max calc for ranges won't work because it'll ignore OPEN spots within the table and visa versa.

Need a select query to identify the start and end of contiguous ranges of extensions with the same Switch/Use/BroadUse/Comments, without modifing the table structure as I don't want to mess with the user interface and table update queries.

If anyone has any idea where to start, that would be immensly helpful, as I'm still pretty new to SQL and Access. This database has been around for many years and the person who made it either never figured this out or didn't bother to notice the problem.

Thanks!

 

[Golom]

Using a simpler case to illustrate the technique
[tt]
NUMBER TEXT
1 A
2 A
3 A
4 B
5 B
6 A
7 A
[/tt]

SELECT A.Number+1 As [Start],  B.Text As [Constant]

FROM myTable A INNER JOIN myTable AS B 
ON B.Number = A.Number+1  AND A.Text <> B.Text

UNION

(Select Number,Text From myTable
Where Number=(Select MIN(Number) From myTable))

UNION

(Select Number+1,Text From myTable
Where Number=(Select MAX(Number) From myTable))

and call that "qryRanges"

SELECT q1.Start, 
       (Select MIN([Start])-1 As [End] From qryRanges
        Where Start > Q1.Start) As [End], 
       Q1.Constant

FROM  qryRanges AS Q1  

WHERE  (Select MIN([Start])-1 As [End] From qryRanges Where Start > Q1.Start) IS NOT NULL

Produces
[tt]

Start End Constant
1 3 A
4 5 B
6 7 A
[/tt]

Note: This will work only of there are no gaps in the numeric sequence when the value of "Text" (in this example) changes.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[kevpete]

Thanks a million.

I've gotten it to work for my data, however it takes FOREVER to execute and actually manages to crash Access a few times. I figure this is a problem with having it parse 10,000 records. Any ideas?

 

[Golom]

Try this JOIN variation

SELECT Q1.Start, MIN([Q2.Start])-1 AS [End], Q1.Constant

FROM qryRanges AS Q1 INNER JOIN qryRanges Q2
     ON Q2.Start > Q1.Start

Group By  Q1.start, Q1.constant

HAVING MIN([Q2.Start]) IS NOT NULL

I ran it on a 10,000 item table in under 2 seconds.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]