missing number in a sequence

[Guest]

I'm trying to write a query that finds a missing number in a sequence, when I
don't know how many numbers are in the sequence. Anybody have any ideas?

 

[Guest]

Type in your number in cell A1. Click on the cell (not in it). Move you
cursor to the lower right corner and when the shape changes from an open
cross to a solid cross, drag it down the worksheet. You will see it
incrementing as you drag it.

 

[Douglas J. Steele]

See my "Access Answers" column in the April, 2004 issue of Pinnacle
Publication's "Smart Access". You can download the column (and sample
database) for free at http://www.accessmvp.com/djsteele/SmartAccess.html

 

[Guest]

One way is the use Excel auto fill to create a list of your numbers and place
in Access. Then create an Unmatched query between that table and your list.

 

[John W. Vinson]

On Thu, 8 Mar 2007 11:13:03 -0800, Meg Brady <Meg

I'm trying to write a query that finds a missing number in a sequence, when I
don't know how many numbers are in the sequence. Anybody have any ideas?

A Self Join will work:

SELECT A.numberfield+1 AS Missing
FROM yourtable AS A
LEFT JOIN yourtable AS B
ON B.numberfield = A.numberfield + 1
WHERE B.numberfield IS NULL;

This will show the first missing value in any block of missing values; of
course you'll always get at least one hit, the infinitely large gap after the
largest value in the table.

John W. Vinson [MVP]

 

[Guest]

Great, thanks! Unfortunatly, I have next to no experience with Excel. Any
advice on how to do the auto fill?

 

[Guest]

Thanks to all three of you - surely one of these will work for me.