Help with Query/Function to find sequential numbers 3

[dkmansion]

My problem is this..

I now manage phone numbers for an institution and need to identify sequential numbers in a table. tblNumbersfldPhoneNumber...and other fields)

The issue is sometimes I need to identify 3 sequential #s sometimes 4 sometimes 10. So the ability to have the tool/Function/query return the number of sequentials I request would be great.

Are there any Ideas along this line?



Donald M

 

[Remou]

Something like (?):

Function Sequential(intCount As Integer, blnOverlap As Boolean)
Dim rs As DAO.Recordset
Dim astrRecs, strSQL
Dim varBookmark
Dim intSeqCount

strSQL = "select id from msysobjects order by id"

Set rs = CurrentDb.OpenRecordset(strSQL)
rs.MoveLast
rs.MoveFirst
varBookmark = rs.Bookmark

Do While Not rs.EOF
    astrRecs = rs.GetRows(intCount)
    
    If astrRecs(0, 0) + (intCount - 1) = astrRecs(0, intCount - 1) Then
        'Debug.Print "Sequence found."
        intSeqCount = intSeqCount + 1
        'For i = 0 To intCount - 1
        '    Debug.Print astrRecs(0, i)
        'Next
    Else
        'Debug.Print "No sequence."
    End If
    
    If blnOverlap Then
        'Back to the beginning
        rs.Bookmark = varBookmark
        rs.MoveNext
        varBookmark = rs.Bookmark
    End If
    
    If rs.RecordCount - rs.AbsolutePosition < intCount Then
        Exit Do
    End If
Loop
Sequential = "Number of sequences: " & intSeqCount
End Function

 

[CautionMP]

dkmansion,
Here is a pure SQL example that will help you to identify if a phone number is part of a sequence. It's based on a single table with the following details:
[tt] Name: tblPhoneNumbers
fld: Phone (double)[/tt]
Populated with the following data:
[tt] 123456780
123456781
123456782
234567890
345678901
456789012
456789013[/tt]

Here is the SQL string for a query that identifies which numbers are part of a sequence:

SELECT x.Phone, Count(y.Phone) AS Seq
FROM tblPhoneNumbers x, tblPhoneNumbers Y
WHERE x.Phone BETWEEN y.Phone - 1 AND y.Phone + 1
GROUP BY x.Phone;

And here is the result of the query:
[tt] Phone: Seq:
123456780 2
123456781 3
123456782 2
234567890 1
345678901 1
456789012 2
456789013 2[/tt]

The [tt]Seq:[/tt] field will tell you where in a sequence the number is.
1: Not part of a sequence
2: The beginning or end of a sequence
3: In the middle of a sequence

Now the hard part, how to use this as a solution to your problem, which I leave up to you.

Hope this helps,
CMP

(GMT-07:00) Mountain Time (US & Canada)

 

[dkmansion]

Remou:
Thanks, your function does return the number of sequences. I also (though not stated in my post need to know what those numbers in the sequence are..) I think my wording should have been more like

I would like to retrieve and display the actual numbers that satisfy 4 , 6, 10 or (what ever amount I need at the time) sequential numbers.

Have the function retrieve the list of numbers that are 4 in a row, and show them to me.


CautionMp's SQL does satisfy the identification of the numbers, though having to clumsily look through the values for the series isn't appealing, and I will continue to search for the application of your code.

Thanks a start to you both but I leave issue open for any other suggestions.

Donald M

 

[Remou]

Did you note the commented section:
'For i = 0 To intCount - 1
' Debug.Print astrRecs(0, i)
'Next
Which, if uncommented, will print the numbers to the immediate window. Like CautionMP, I could not see what you wanted to do with the sequences, so I simply debug.printed them.

 

[Remou]

I supose I should mention that if you set blnOverlap to true, from:

12
13
14
15

You will get 2 sequences of three:
12
13
14

13
14
15

 

[dkmansion]

I took another look and yes that helps identify the numbers greatly. I went on to add some code in the loop to deposit the numbers into a table for use. I will add some to display them on a form to choose the values the user or I need and then mark them as used.

here is the application I have for your function now. I didn't add error handling yet but here it is, Thanks much. Donald

Function Sequential(intCount As Integer, blnOverlap As Boolean)
Dim rs As DAO.Recordset, [blue]rs2 As DAO.Recordset[/blue]
Dim astrRecs, strSQL, [blue]strSqlInsert[/blue]

Dim varBookmark
Dim intSeqCount
[blue]
strSQL = "SELECT a.Pagernumbers" _
    & " FROM InhouseNumbers_1 as a" _
    & " WHERE (((a.Used)=False))" _
    & " ORDER BY a.Pagernumbers;"

DoCmd.RunSQL "delete * from locavailablePagerNumbers"
docmd.setwarnings false
strSqlInsert = "select * from LocAvailablePagerNumbers"
[/blue]
Set rs = CurrentDb.OpenRecordset(strSQL)
[blue]Set rs2 = CurrentDb.OpenRecordset(strSqlInsert)[/blue]

rs.MoveLast
rs.MoveFirst
varBookmark = rs.Bookmark

Do While Not rs.EOF
    astrRecs = rs.GetRows(intCount)
    
    If astrRecs(0, 0) + (intCount - 1) = astrRecs(0, intCount - 1) Then
        'Debug.Print "Sequence found."
        intSeqCount = intSeqCount + 1
        For i = 0 To intCount - 1
        [blue]'Use With rs2 to insert records into temp table
        With rs2
                .AddNew
                !AvailableNumber = astrRecs(0, i)
                !seqNumber = intSeqCount
                .Update
        End With[/blue]
            'Debug.Print astrRecs(0, i) & vbTab & intSeqCount
        Next

    Else
        'Debug.Print "No sequence."
    End If
    
    If blnOverlap Then
        'Back to the beginning
        rs.Bookmark = varBookmark

        rs.MoveNext
        Debug.Print Sequential
        varBookmark = rs.Bookmark
    End If
    
    If rs.RecordCount - rs.AbsolutePosition < intCount Then
        Exit Do
    End If
Loop
Sequential = "Number of sequences: " & intSeqCount
    [blue]rs2.Close
docmd.setwarnings true[/blue]

End Function

Donald M

 

[dkmansion]

Remou,

I noticed that this function is skipping the first couple of numbers in each sequence. example
7421
7591
7592
7593
7594
7595
7596
7597
7598
7599
7600
7601
7602.../...

When I run sequential(5,false) it returns
7593
7594
7595
7596
7597

Shouldn't I be getting:
7591-7595 for seq1
7596-7600 for seq2

Also
Seq15 Starts at 7669 skipping 7667&7668
Seq28 Starts at 7748 skipping 7746&7747
Seq33 Starts at 7789 Skipping 7785-7788

Any Thoughts?


Donald M

 

[PHV]

...
varBookmark = rs.Bookmark
[!]blnRestart As Boolean[/!]
Do While Not rs.EOF
astrRecs = rs.GetRows(intCount)
If astrRecs(0, 0) + (intCount - 1) = astrRecs(0, intCount - 1) Then
'Debug.Print "Sequence found."
intSeqCount = intSeqCount + 1
For i = 0 To intCount - 1
'Use With rs2 to insert records into temp table
With rs2
.AddNew
!AvailableNumber = astrRecs(0, i)
!seqNumber = intSeqCount
.Update
End With
'Debug.Print astrRecs(0, i) & vbTab & intSeqCount
Next
[!]blnRestart = False[/!]
Else
'Debug.Print "No sequence."
[!]blnRestart = True[/!]
End If
If blnOverlap [!]Or blnRestart [/!]Then
'Back to the beginning
rs.Bookmark = varBookmark
rs.MoveNext
Debug.Print Sequential
varBookmark = rs.Bookmark
End If
...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[dkmansion]

PHV, This returns the info in a completely different way than I thought it should (much more lines and sequences returned) but I am definitely able to get the info I need with this. Thanks again.

Donald M