Find non sequential numbers

  • Thread starter Thread starter Tara
  • Start date Start date
T

Tara

Is there a way to find numbers that are missing from a group? For instance,
find numbers that don't follow sequence. We have a receipts database to
track sales of a fundraising item. I need to figure out which receipts are
missing from the group so that we know whether or not there are outstanding
receipts that our staff haven't turned in yet.

Thanks!
 
Is there a way to find numbers that are missing from a group?  For instance,
find numbers that don't follow sequence.  We have a receipts database to
track sales of a fundraising item.  I need to figure out which receiptsare
missing from the group so that we know whether or not there are outstanding
receipts that our staff haven't turned in yet.

Thanks!
Click to expand...

If it can be assumed that there are not a bunch of missing receipts,
you could try this:

SELECT A.[Receipt#] + 1 as [Missing Receipt]
from tblSales as A
where not exists
( select 'true'
from tblSales
where [Receipt#] = A.[Receipt#] + 1 ) ;

However, it will only select the first of a missing sequence of
multiple missing receipts.
Further, there may be some missing receipts before the first one
found.
 
Thanks for the response Lou. I'll give it a try and let you know how it goes!

Lou said:
Is there a way to find numbers that are missing from a group? For instance,
find numbers that don't follow sequence. We have a receipts database to
track sales of a fundraising item. I need to figure out which receipts are
missing from the group so that we know whether or not there are outstanding
receipts that our staff haven't turned in yet.

Thanks!
Click to expand...

If it can be assumed that there are not a bunch of missing receipts,
you could try this:

SELECT A.[Receipt#] + 1 as [Missing Receipt]
from tblSales as A
where not exists
( select 'true'
from tblSales
where [Receipt#] = A.[Receipt#] + 1 ) ;

However, it will only select the first of a missing sequence of
multiple missing receipts.
Further, there may be some missing receipts before the first one
found.
Click to expand...
 
I must have done something wrong Lou. When I try to run the query, nothing
happens. Should the SQL statement look EXACTLY as you typed it?



Lou said:
Is there a way to find numbers that are missing from a group? For instance,
find numbers that don't follow sequence. We have a receipts database to
track sales of a fundraising item. I need to figure out which receipts are
missing from the group so that we know whether or not there are outstanding
receipts that our staff haven't turned in yet.

Thanks!
Click to expand...

If it can be assumed that there are not a bunch of missing receipts,
you could try this:

SELECT A.[Receipt#] + 1 as [Missing Receipt]
from tblSales as A
where not exists
( select 'true'
from tblSales
where [Receipt#] = A.[Receipt#] + 1 ) ;

However, it will only select the first of a missing sequence of
multiple missing receipts.
Further, there may be some missing receipts before the first one
found.
Click to expand...
 
I must have done something wrong Lou.  When I try to run the query, nothing
happens.  Should the SQL statement look EXACTLY as you typed it?



If it can be assumed that there are not a bunch of missing receipts,
you could try this:
Click to expand...
SELECT A.[Receipt#] + 1 as [Missing Receipt]
from tblSales as A
where not exists
( select 'true'
from tblSales
where [Receipt#] = A.[Receipt#] + 1 ) ;
Click to expand...
However, it will only select the first of a missing sequence of
multiple missing receipts.
Further, there may be some missing receipts before the first one
found.- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

This was a model of how I thought it could be done. You would need to
use your own table and column names.

My thinking was as follows:

Find those Receipt# for which there did not exist one in the next
numeric order.

I recognized that in the example of Receipt# 2,3,7,9 existing, the
query would only find 4 and 8 to be missing.
 
Back
Top