gaps in the sequence order

[Chris Savedge]

We have a table here that uses auto num to track records. Obviously these
numbers should be in sequential order but due to the occasional system
problems there are gaps in the sequence order. I need to find the gaps
(missing numbers) in the sequence. Any help is greatly appreciated.

Thanks,
Chris

 

[Bas Cost Budde]

We have a table here that uses auto num to track records. Obviously these
numbers should be in sequential order but due to the occasional system
problems there are gaps in the sequence order. I need to find the gaps
(missing numbers) in the sequence. Any help is greatly appreciated.

Thanks,
Chris

Get the qryThousand from my site (under Articles:Range, scroll down a
bit) expand it as you wish and left join that on your table.

 

[Cheryl Fischer]

Chris,

The purpose of an AutoNumber field is to create a system-generated,
guaranteed unique number which may be used to identify a particular record.
While one can set the Autonumber field to increment, there is absolutely no
guarantee that the numbers will, over time, be sequential with no gaps. In
addition to the fact that if a record is deleted, a gap will occur in the
sequence of autonumbers, also, once generated - even if the record is not
saved - an autonumber is never re-used.

If your application requires the generation of sequential numbers, you will
need to create your own custom numbering system.

 

[Chris Savedge]

Thanks Cheryl. I understand what you are saying and totally agree. However,
this is an old table and I still need to find where the records skip a
number. Do you have any suggestions?

Thanks,
Chris

 

[Cheryl Fischer]

There is a technique described in the attached link which will show you how
to create a report with "sequence gaps" flagged.

ACC: How to Flag Out-of-Sequence Numbers on a Report
http://support.microsoft.com/default.aspx?scid=kb;en-us;137118


hth,

 

[Chris Savedge]

Thanks so much Cheryl.

There is a technique described in the attached link which will show you how
to create a report with "sequence gaps" flagged.

ACC: How to Flag Out-of-Sequence Numbers on a Report
http://support.microsoft.com/default.aspx?scid=kb;en-us;137118


hth,