I need an expression to detect missing numbers.

[Frank Martin]

Mainly to find unpresented cheques. At the end of a period there is stored
a large number of cheque numbers and I need an expression to detect when one
is missing from the series.
Perhaps something like "if there is a number missing in a sequence then
store this missing number" in a query column.
Please help, Frank

 

[Jeff Boyce]

Frank

A couple approaches, plus a suggestion.

One approach would be to have a table that contains all the numbers that are
supposed to be there, and build an "unmatched" query between your list of
actual cheques and those supposed to be there.

Another idea would be to write a procedure that steps through the actual
cheque numbers, incrementing by 1. When an "expected" number isn't found,
it could be written to a recordset.

A suggestion: if you write missing numbers to a table, you'll also need to
have a way to clear missing numbers from that table once found. Rather than
"write" a "calculated" value, you could just run your routine (see above for
two possibilities) to identify missing numbers on the fly, without any need
for building additional synchronization.

Good luck

Jeff Boyce
<Access MVP>

 

[Tom Ellison]

Dear Frank:

A subquery in a criteria using WHERE NOT EXISTS could filter to where a
check number N-1 (where N is the current check number). Another subquery in
the SELECT clause could find the MAX() existing check number that is less
than the current check number. This then gives a range of missing check
numbers.

I've done this before and it is effective in producing a list of all the
ranges of missing check numbers. It may need some tweaking, such as
eliminating the range of check numbers less than the MIN() check number that
exists.

For more details, please provide a simple query that shows everything else
you want to see and I'll try to add the ranges of missing numbers.

 

[Frank Martin]

Thank you all for the help. This will get us started.
Regards Frand.

 

[Michel Walsh]

Hi,


I see you have already many good solutions. Here is another one. Rank (am I first, second, third,
.... ) each record accordingly to the cheque number. Subtract the rank from the cheque number,
group by on that result, take the min and the max of the cheque number.


SELECT Min(cheque), Max(cheque)
FROM myTable
GROUP BY cheque-DCount("*", "MyTable", "cheque<=" & cheque)


ex.

cheque rank cheque-rank
1 1 0
2 2 0
5 3 2
6 4 2
7 5 2
9 6 3
10 7 3
....


so, min(cheque), max(cheque) will supply:


1 2
5 7
9 10


ie, there is printed cheque from 1 to 2, 5 to 7, and 9 to 10.
(so, missing, 3 to 4, and 8 to 8 )


Hoping it may help,
Vanderghast, Access MVP