DataBase - Identify missing numbers

Asked By Tara

20-Nov-09 01:50 PM

I have a table that uses a text field to store receipt numbers.  Currently I
use VAL to sort the numbers numerically.  Is there a way to identify which
numbers are missing out of the sequence?  For instance if my receipts start
at 1 and go through 10,000, but receipts 900 through 987 are missing, can I
get a query to show me that?

Thanks!

20-Nov-09 02:41 PM

Tara

By "missing" I asssume you mean "should be in a list but is not"...

One way to do this is to have a list of what numbers SHOULD be listed, then
use the query wizard to construct an "unmatched" query between your list and
the SHOULD BE list to see which of the SHOULD BE values are not in your
list.

Or you could create a procedure that steps through the sequence numbers
one-by-one, looking for situations in which the next (expected) value is not
present, saving all those up, then displaying those after running through
your list.

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

20-Nov-09 03:35 PM

Try this --
WO8SEP09 is the table and Work is the number field.  The first query finds
the first missing number in a sequence and the second find the end of that
sequence.

first missing number of each sequence --
SELECT [Work]+1 AS Missing
FROM WO8SEP09 AS T
WHERE (((Exists (SELECT *       FROM WO8SEP09 T1       WHERE T1.[Work] =
T.[Work] + 1))=False));


SELECT [first missing number of each sequence].Missing AS Missing_Start,
(SELECT TOP 1 [XX].Work FROM WO8SEP09 AS [XX] WHERE [XX].Work > [first
missing number of each sequence].Missing ORDER BY [XX].Work)-1 AS Missing_End
FROM [first missing number of each sequence];

--
Build a little, test a little.

20-Nov-09 03:41 PM

Construct a table with one number field and 10 records with the numbers 0 to 9.

Construct a query using that table multiple times to generate numbers from
zero to 10,000

SELECT CStr(T1.NumField + T10.NumField*10 + T100.NumField * 100 +
T1000.Numfield*1000) as NumString
FROM NumTable as T1, NumTable As t10, NumTable as T100, NumTable as T1000

Now you can use that and your store reciept table to do an unmatched query.

SELECT NumString as MissingValues
FROM QueryNumString LEFT JOIN [ReceiptsTable] as R
ON QueryNumString.NumString = R.[ReceiptNumber]
WHERE R.[ReceiptNumber] is Null

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

20-Nov-09 03:55 PM

Thank you both for the suggestions!
"Tara" wrote: