Searching for Multiple sequential free records - HELP

[Fusion008]

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)] [Assigned Yes/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

(Sorry for the ugly formatting. could not work out how to post it properly)

In this table I have around 60,000 records. The table lists all of my IP addresses in order.

I would like to know , if it's possible to create a query that can search through this table for a set number of free sequential records 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) records that have false in the assigned column.

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, the first 8 free addresses cannot be used for a subnet of 8 because they do not start on a boundary 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.

Is a query the best way to do this? Is there a better way to do it?

Even if I could get an example of how to do either of these steps, it would be a place to start from.

Thanks

[pkstormy]

My first sense on this is to create 4 fields which would break 10.10.1.6 into 4 fields (probably integer types) which has 10, 10, 1 and 6 in it (or it could be broken apart in a query). This would make it a little more managable for running a query to do what you want it to do (without writing a fairly long line of code or function). This query could then be used in further queries to disect it more. Otherwise a function could be designed to break the IP apart and disect it for missing/unused ones.

[Fusion008]

Pkstormy, first thanks for the response.

My actual table has 6 fields. In my description I left out the other fields, thinking it would make my request easier to explain.

I have the four octets of the IP address in seperate fields, all INT. 1 field for assigned (yes/no) and then a field that is actually used as my primary key, but it's actually the IP octets contatenated together to give me a unique INT, 172054193020 for the IP octets 172.054.193.020.

I was assuming I could use a bit-wise calculation on the IP address and mask to give me the IP range, but from some quick googling it would appear that Access does not really like storing binary or doing binary calculations. If anyone knows of a way though, that would be great. Don't mind how I get to the destination as long as I get there....

Pkstormy, did you have something in mind for how I could find free address ranges with the seperate fields?