Compare sequential values of a column between rows to find gaps.

[Spectra]

Im fairly new to access so please bare with me if I sound out of place. I've
tried searching but perhaps I do not know how to phrase the question to get
relevant results.

I have a table with column containing sequential numbers called Charge ID
(basically a unique 'order number'). I need to find out which Charge ID's
have been deleted creating a gap of 1 or more in the sequence and write those
deleted order numbers into a new table. The column looks similar to this;

Charge ID
100
102
103
110

From what I have been told this cannot be accomplished using either a sql
query or the query builder, rather that I would have to use VB because this
operation requires a cursor to 'look' at the next row. I have no idea where
to begin. Any suggestions or pointers would be greatly appreciated.

 

[John Vinson]

Im fairly new to access so please bare with me if I sound out of place. I've
tried searching but perhaps I do not know how to phrase the question to get
relevant results.

I have a table with column containing sequential numbers called Charge ID
(basically a unique 'order number'). I need to find out which Charge ID's
have been deleted creating a gap of 1 or more in the sequence and write those
deleted order numbers into a new table. The column looks similar to this;

Charge ID
100
102
103
110

From what I have been told this cannot be accomplished using either a sql
query or the query builder, rather that I would have to use VB because this
operation requires a cursor to 'look' at the next row. I have no idea where
to begin. Any suggestions or pointers would be greatly appreciated.

There are sneaky Self Join queries to do this, but the simplest way is
to create a table, Num, with one field N with values from 1 through
the largest value of Charge ID. You can build this quickly in Excel
using fill-down and just copy and paste it (or Import it) into an
Access table. An Unmatched Query Wizard query will find your missing
values.

John W. Vinson[MVP]

 

[Spectra]

Worked like a charm! Thank you very much John.

There are sneaky Self Join queries to do this, but the simplest way is
to create a table, Num, with one field N with values from 1 through
the largest value of Charge ID. You can build this quickly in Excel
using fill-down and just copy and paste it (or Import it) into an
Access table. An Unmatched Query Wizard query will find your missing
values.

John W. Vinson[MVP]