Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2011
    Posts
    2

    Question Unanswered: Query for contiguous records with a particular condition set???

    I am a total beginner with MS Access 2003 and all suggestions are gratefully recieve. I have a table that looks like this: -

    [Record Number, INT (PK)] [IP Address varchar(50)] [AssignedYes/No]
    1 10.10.1.0 Y
    2 10.10.1.1 Y
    3 10.10.1.2 N
    4 10.10.1.3 N
    5 10.10.1.4 N
    6 10.10.1.5 N
    7 10.10.1.6 N
    8 10.10.1.7 N
    9 10.10.1.8 N
    10 10.10.1.9 N
    11 10.10.1.10 N
    12 10.10.1.11 N

    In this table I have around 60,000 records. The table lists all of my IP addresses in order. I would like advice, if it's possible
    to create a query that can search through this table for a set number of free contiguous addresses that have not been assigned.

    So If I need a set of 6 free addresses, I would like to be able to search the table for 8 (6 hosts+1 subnet+1 broadcast) Unassigned contiguous addresses/records.

    That's the first part to the challenge, the second is that the search would actually have to start it's checks in blocks of 8 too so that the subnet will actually work when someone tries to use them.
    In the example above, there are 8 free addresses but I cannot use them for a subnet of 8. I would need the query to try searching from 0 for 8, then jump to record 9 and look for 8 and keep jumping til it finds 8 contiguous records starting from one of the records that are a multiple of 8.

    I hope this makes sense to someone that can help.

    PS. Sorry about the dreadful formatting of the columns.

  2. #2
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Why can't you simply open it as an opentable, and do a ".Move 8" every time it comes up against a 'Y'? BTW, I hope you have it indexed on the PK.

    Sam

  3. #3
    Join Date
    Jun 2011
    Posts
    2
    Sam,

    Thanks for the reply. I am not sure what you mean by use an opentable and do a ".move 8" each time it comes across a Y. Databases aren't really my area, in fact this is the first time I have created one in about 10 years.

    I did index the PK with no duplicates.

  4. #4
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    If you require them to be allocated in groups from a known starting point, it might be worth creating two new fields within the database. One to flag "parent" addresses as such, the other to hold each address' parent. Then you could write a query that joins two copies the table together, to show unallocated parent addresses and their unallocated children. You could then base a query on that one that counts the number of children per parent.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  5. #5
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Here's a code snippet I was referring to.

    Code:
    Dim Rst As DAO.Recordset, RCntr As Long
    
        Set Rst = CurrentDb.OpenRecordset("TableName", dbOpenTable)
        With Rst
            .Index = "IndexName"
            .MoveFirst
            Do While Not .EOF
    
    StartHere:
    
                If !Assigned Then
                    .Move 8
                    GoTo StartHere
                Else
                    RCntr = 0
                    Do While Not !Assigned
                        RCntr = RCntr + 1
                        If RCntr Mod 8 = 0 Then 'There are at least 8 consecutive unassigned numbers
                            'Do Something With The Information
                        End If
                    Loop
                End If
    etc.
    I deliberately didn't finish the sub, and didn't show the fine points, such as checking for EOF, etc. This is not a difficult problem, but this is the basic approach I would use. In any event, don't be afraid to use the Help file; it's good.

    Sam

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •