Prev: olaNext: 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: olaNext: Macro Variables