Query to count between list of number (Predicting Start/End that mayoccur in data range)

[Angela]

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.

 

[XPS350]

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.

A query to do this could look like:

SELECT Count(YourNumber) AS CountOfNumbers
FROM YourTable
WHERE YourNumber>=[Enter Start] And YourNumber<=[Enter End];

Groeten,

Peter
http://access.xps350.com

 

[Risse]

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.

 

[Tokyo Alex]

Hi Angela,

I'm not sure how (or if, even) you could do that in a query.

Here is some (quickly tested) code that seems to give the results you want:
'********VBA code (somewhat tested)**********
Public Sub StartEnd()

Dim dbs As DAO.Database: Set dbs = DBEngine(0)(0)
Dim rstSource As DAO.Recordset
Dim rstTarget As DAO.Recordset
Dim lngCount As Long
Dim lngStart As Long
Dim lngEnd As Long
Dim lngPrev As Long
Dim strSQL As String

strSQL = "SELECT setField FROM tblStartEndTest ORDER BY setField"

Set rstSource = dbs.OpenRecordset(strSQL, dbOpenDynaset)
Set rstTarget = dbs.OpenRecordset("tblStartEndResults", dbOpenDynaset)

With rstSource
.MoveFirst
lngStart = !setField
lngPrev = lngStart
lngCount = 1
.MoveNext
Do
If !setField = lngPrev + 1 Then
lngPrev = !setField
lngCount = lngCount + 1
Else
lngEnd = lngPrev
With rstTarget
.AddNew
!serStart = lngStart
!serEnd = lngEnd
!serCount = lngCount
.Update
End With
lngStart = !setField
lngPrev = lngStart
lngCount = 1
End If
.MoveNext
Loop Until .EOF
lngEnd = lngPrev
With rstTarget
.AddNew
!serStart = lngStart
!serEnd = lngEnd
!serCount = lngCount
.Update
End With
End With

rstSource.Close
Set rstSource = Nothing
rstTarget.Close
Set rstTarget = Nothing
Set dbs = Nothing

End Sub
'********End VBA Code********************

Copy and paste this into a standard module.

If you need any help following what it does, or getting it to work, please
post back.

Cheers,
Alex.

 

[stumac]

Hi Angela - are you always grouping in 10's? If so then the partition
function would do it in a query:

SELECT Min(Myfield) AS Start, Max(Myfield) AS [End], Count(Myfield) AS
Quantity
FROM MyTable
GROUP BY Partition([myfield],0,1000000,10);


Hth

Stu

 

[John Spencer]

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

 

[John Spencer]

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

 

[Angela]

Hello Alex..

Apologies for late reply..

Well you code works. It creates a table "tblStartEndResults" and is
counting between start and end values from a list in table
"tblStartEndTest".

I have start and end values as text since some figures start with a
zero. With your script, we are dealing the values as numbers and not
text.

What I think, we can do the calculation based on your fields which
should be read from the text fields as numbers and in the
"tblStartEndResults", we can show the corresponding original text
fields also.

Like for instance...

setfield txtfield
34 034
35 035
36 036
37 037
41 041
42 042
40 040

-----------------------------------
Result
serstart serend textfieldstart textfieldend sercount
34 37 034 037 4
40 42 040 042 3


We could also just use serstart & serend for calculation and hide them
in result query by just showing the rest of the fields.
textfieldstart textfieldend sercount
034 037 4
040 042 3


Hope I have managed to explain it clearly.

 

[Angela]

Hey John,

Your query approach is good but I'm still checking it with the text
field output.

Will get back to you on this one.