Home  |   French  |   About  |   Search  | mvps.org

 Queries: Merging sequences Author(s) Michel Walsh 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)