I have a database of item numbers, but they are not all contiguous. I'd like to be able to run a query that finds all item numbers that are not in the continuous run, or in other words item numbers where both number - 1 and number + 1 exist. This is an Access database and the only way I can think of doing this is actually looping through all possible numbers and checking to see if each one exists. As you can imagine, the overhead on such an operation would be prety high.

Thanks for any input,
Ben Hughes

Originally posted by SynHost
I have a database of item numbers, but they are not all contiguous. I'd like to be able to run a query that finds all item numbers that are not in the continuous run, or in other words item numbers where both number - 1 and number + 1 exist. This is an Access database and the only way I can think of doing this is actually looping through all possible numbers and checking to see if each one exists. As you can imagine, the overhead on such an operation would be prety high.

Thanks for any input,
Ben Hughes

Please state this more clearly. For instance I find the following to be contradictory:

"I'd like to be able to run a query that finds all item numbers that are not in the continuous run, or in other words item numbers where both number - 1 and number + 1 exist."

To me that would be an item number that IS in the "continuous run" (I'm not sure what you mean by this however). For instance, re: item #2, if 1 and 3 exist, they are all continuous.

Perhaps an example from your specific item numbers would help. And what are the "bounds" of your data? In other words, what are the lowest and highest possible item numbers? And the lowest and highest existing item numbers? And the total number of item numbers currently in your database.

agree with jb4mt, I have no clue what you are asking. Some simple sample data from your table may be good and what you are trying to grab from that table.

He is saying he would like to have a script that tells him all the item numbers not in use.

For example;

1
2
4
5
7
9
14
16
18

His script would report:
3,6,8,10,11,12,13,15,17

This would be easy to code in almost any backend language, however, I don't know if a SQL solution exists for such a problem.

Yes ruler, that's exactly what I mean.

I guess I'll just have to return a complete resultset and loop through it on the serverside.

Originally posted by SynHost
Yes ruler, that's exactly what I mean.

I guess I'll just have to return a complete resultset and loop through it on the serverside.

Order your result set of course