Queries: Merging sequences

Assuming you have some sequences, From-To, like:

SequenceA		' table name
Who, [From],[To]	' fields name
A, 1, 1
B, 17, 20
B, 21, 23
B, 26, 29
B, 30, 34
B, 35, 39
C, 1, 11
C, 12, 12
C, 13, 45
C, 46, 49
C, 50, 76

and you want merge whatever can be, here, to get the result:

A, 1, 1
B, 17, 23
B, 26, 39
C, 1, 1
C, 12, 76

We can make a first query, bringing together the [From] and the [To]+1

SELECT Who, [From] As Origine
FROM SequenceA

UNION ALL

SELECT Who, [To]+1
FROM SequenceA;

and we save it as QUseq.

Then, we make a second query, to count the number of occurence for each number:

SELECT QUseq.Who, QUseq.Origine
FROM QUseq
GROUP BY QUseq.Who, QUseq.Origine
HAVING (((Count(QUseq.Origine))<>2))
ORDER BY QUseq.Who, QUseq.Origine;

and we save it under the name QUseq1. Sure, the logic is simply to note we have got a count of 2 if the sequence is consecutive ([To]+1 = [From]); the count will be two because we have used ALL in the UNION.

The query returning only those numbers not having a count of two, the query return all the new "limits", in our example:

A, 1
A, 2
B, 17
B, 24
B, 26
B, 40
C, 1
C, 77

which will be translated as required to:

A, 1, 1
B, 17, 23
B, 26, 39
C, 1, 76

with a last query:

SELECT QUseq1.Who, QUseq1.Origine, DMin("Origine","QUseq1","Who='" &
[Quseq1].[Who] & "' AND Origine >" & [Origine])-1 AS Expr1
FROM QUseq1 INNER JOIN SequenceA ON (QUseq1.Origine = SequenceA.From) AND
(QUseq1.Who = SequenceA.Who);

where we use DMin()-1 to undo our initial work on [To]+1. The inner join is required to keep only the real sequences (to remove a false one like B, 24, 25 in our example)