Prev: ola
Next: Macro Variables
From: John Spencer on 2 Apr 2010 16:38
I should have tested first. Here is a query that seems to work.

My test table was named tNumbers and my field was named Counter.


SELECT QStart.Counter as StartNumber,
Min(qEnd.Counter) as EndNumber,
Min(qEnd.Counter) - qStart.Counter + 1 as Amount
FROM
(SELECT A.Counter
FROM tNumbers as A LEFT JOIN tNumbers as B
ON A.Counter = B.Counter+1
WHERE B.Counter Is Null) As QStart
INNER JOIN
(SELECT A.Counter
FROM tNumbers as A LEFT JOIN tNumbers as B
ON A.Counter = B.Counter-1
WHERE B.Counter Is Null) As qEnd
ON qStart.Counter <=qEnd.Counter
GROUP BY qstart.Counter

When I tested I found that I had reversed the queries that got the begin and
end of the sequence. AND I had the math to calculate quantity wrong.

If your table and field names don't follow the naming guidelines you will
probably have to nest queries instead of using sub-queries in the from clause.
Table and field names should start with a letter and only contain letters,
numbers, and the underscore - no spaces or other characters.


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

John Spencer wrote:
> You might try this untested idea.
>
> Find the beginning of a sequence
> SELECT A.Number
> FROM Table as A LEFT JOIN Table as B
> ON A.Number = B.Number-1
> WHERE B.Number is Null
>
> Find the End of a sequence
> SELECT A.Number
> FROM Table as A LEFT JOIN Table as B
> ON A.Number = B.Number+1
> WHERE B.Number is Null
>
> SELECT qBegin.Number as StartofRange,
> Min(qEnd.Number) as EndOfRange,
> 1+QBeginNumber-Min(qEndNumber) as Quantity
> FROM qBegin INNER JOIN qEnd
> ON qBegin.Number <= qEnd.Number
> GROUP BY qBegin.Number
>
>
> John Spencer
> Access MVP 2002-2005, 2007-2010
> The Hilltop Institute
> University of Maryland Baltimore County
>
> Angela wrote:
>> Hi,
>>
>> I have a below list of numbers.
>>
>> 566667
>> 566668
>> 566669
>> 566665
>> 566666
>> 566671
>> 566672
>> 566680
>>
>>
>> I want a query that would return a count between start and end of
>> range.
>>
>> Like
>>
>> Start End Quantity
>> 566665 566669 5
>>
>> 566671 566672 2
>>
>> 566680 566680 1
>>
>>
>> Thank you.
 | 
Pages: 1
Prev: ola
Next: Macro Variables