How do I Query range of Numbers?

[JohnPatrickPH]

Hello and Good day everyone, I have range of numbers in my table:

fld_CT_No
900001
900002
900003
900004
900005
900007
900010
900011
900012
900100

I would like to summarize (make a query) and display it this way:

Numbers Issued:
900001 to 900005 5
900007 to 900007 1
900010 to 900012 3
900100 to 900100 1
---------------------
Total Records--> 10

the following code works for me, but It is on a "table way"
I want it in query. If query is not possible, can you help me improve the following code?

Private Sub cmd_Make_Series_Click()

'On Error GoTo Err_Handler
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rs1 As DAO.Recordset
Dim lngCT_No As Long
Dim lngStart As Long
Dim lngEnd As Long
Set db = CurrentDb()
Set rs = db.OpenRecordset("tbl_CT", dbOpenDynaset)
Set rs1 = db.OpenRecordset("tbl_Series", dbOpenDynaset, dbAppendOnly)


If rs.RecordCount >= 1 Then

rs.MoveFirst
lngStart = rs!fld_CT_No
lngEnd = rs!fld_CT_No

Do
lngCT_No = rs!fld_CT_No
lngEnd = rs!fld_CT_No

rs.MoveNext

If rs.EOF Then
rs1.AddNew
rs1!fld_Series_Start = lngStart
rs1!fld_Series_End = lngEnd
rs1.Update
GoTo skip
End If

If rs!fld_CT_No = lngCT_No + 1 Then

skip:
Else
rs1.AddNew
rs1!fld_Series_Start = lngStart
rs1!fld_Series_End = lngEnd
rs1.Update
lngStart = rs!fld_CT_No

End If

Loop Until rs.EOF

Else

MsgBox "No Series to Create"

End If

rs.Close
rs1.Close
Me.[frm_Series_subform].Requery

Exit_Handler:
Set rs = Nothing
Set rs1 = Nothing
Set db = Nothing
Exit Sub

Err_Handler:
MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "cmd_Make_Series_Click()"
Resume Exit_Handler
End Sub

Thank you so much.

 

[Remou]

You can create a Range table like so:

[tt]RangeName RangeLower RangeUpper
900001 to 900005 900001 900005
900006 to 900007 900006 900007
900010 to 900012 900010 900012
900100 to 900100 900100 900100[/tt]

[tt]SELECT tblRange.RangeName, Count(tblTable.fld_CT_No) AS CountOffld_CT_No
FROM tblTable, tblRange
WHERE tblTable.fld_CT_No Between [RangeLower] And [RangeUpper]
GROUP BY tblRange.RangeName;[/tt]

 

[PHV]

What about this (SQL code) ?

SELECT S.fld_CT_No AS Series_Start,Min(E.fld_CT_No) AS Series_End,Min(E.fld_CT_No)-S.fld_CT_No+1 AS Numbers
FROM (
SELECT A.fld_CT_No
  FROM tbl_CT AS A LEFT JOIN tbl_CT AS B ON A.fld_CT_No=B.fld_CT_No+1
 WHERE B.fld_CT_No Is Null
) AS S INNER JOIN (
SELECT A.fld_CT_No
  FROM tbl_CT AS A LEFT JOIN tbl_CT AS B ON A.fld_CT_No=B.fld_CT_No-1
 WHERE B.fld_CT_No Is Null 
) AS E ON S.fld_CT_No<=E.fld_CT_No
GROUP BY S.fld_CT_No

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886