Finding missing numbers in a field

[Ramesh]

Hi,

In a table with a field which contains a series of numbers say 500 to 700,
can I make a query to find the missing numbers between these two numbers?

Thanks

Ramesh

 

[John Vinson]

Hi,

In a table with a field which contains a series of numbers say 500 to 700,
can I make a query to find the missing numbers between these two numbers?

Thanks

Ramesh

One simple way is to create a little "utility" table - I now do so
routinely in most databases, it comes in handy for many things. The
table is named Num with one Long Integer field N; fill it with values
from 1 to 10000 or so (you can use Excel fill-down and copy and paste
to do this very quickly).

Create a Query using the Unmatched Query wizard to find all records in
NUM that are missing in your table, using a range

BETWEEN 500 AND 700

on N to control what range it searches.

 

[Ramesh]

Thanks very much John. Creative effective solution!!

Warm regards
Ramesh