Find missing numbers

[Gary Dolliver]

Hi all,
Is it possible to find missing numbers from a sequence? For example, if my
table data (in a column) is 1,2,3,4,6,7,8,9,11,12... the desired result
would be 5,10
Is this something that can be done? A while back, someone mentioned setting
up another table that has the complete number sequence and then do a
comparison, but is there something else that can be done?
We have some buggy web order importing going on, and I want to be able to
catch these misses so they are not forgotten.
Help is appreciated, thanks
-gary

 

[Tom van Stiphout]

On Wed, 26 Nov 2008 20:15:01 -0800, Gary Dolliver

The other option is a few lines of VBA code to loop over the recordset
and compare the value read with the expected value.

-Tom.
Microsoft Access MVP

 

[Mike Painter]

Yes but...
For I = StartSeqNum to EndSeqNum
If I <> someTable.OrderNumber then
add I to an exception table
I = I + 1
End If
Next I.
Is a rough idea, but...
You probably can't determine if the first or last number is missing until
you get the next batch.
I've not tried incrementing a counter in a loop since mbasic on a TRS-80 and
it was not always safe then.

The table idea is far safer and a simple query would show what is missing
and could help with duplicates, open orders, etc.

In the mean time please send me one of everything you sell on a missing
order number

 

[John W. Vinson]

Hi all,
Is it possible to find missing numbers from a sequence? For example, if my
table data (in a column) is 1,2,3,4,6,7,8,9,11,12... the desired result
would be 5,10
Is this something that can be done? A while back, someone mentioned setting
up another table that has the complete number sequence and then do a
comparison, but is there something else that can be done?
We have some buggy web order importing going on, and I want to be able to
catch these misses so they are not forgotten.
Help is appreciated, thanks
-gary

One way is to use a Self Join query to find the first value in each gap:

SELECT A.fieldname + 1 AS StartOfGap
FROM yourtable AS A LEFT JOIN yourtable AS B
ON A.fieldname + 1 = B.Fieldname
WHERE B.fieldname IS NULL;

A more complex query can find the entire gap, but it's late and I'm sleepy...

 

[Gary Dolliver]

Thank you John, as always, you are a life saver
-gary

 

[Gary Dolliver]

thank you for the post and ideas! Thankfully, the web site is still testing
and I just wanted to have a type of back up just to double check and be sure
all was coming in correctly