Finding missing No

[sbaud2003]

I want to find all the missing IDs in a table starting from 1 to the end of the Table. How can i do that and display the missing IDs
IDs are Primary and serially numbered. however one missed the seriality in entering.

 

[bob fitz]

Are the ID's of "Auto-Number" type

 

[sbaud2003]

Are the ID's of "Auto-Number" type

yes

 

[ebs17]

Something can only be missing if you have an idea of completeness. Therefore you would have to compare your table against a complete table.

SELECT N.Num
FROM NumberTable AS N LEFT JOIN YourTable AS Y
ON N.Num = Y.ID
WHERE Y.ID Is Null

 

[bob fitz]

yes

Then why would you need to know which are missing. They should have no meaning to a user.

 

[jdraw]

Here is some info about autonumbers from UtterAccess.

What they are NOT:
1. Row (record) sequence numbers.
2. An "order of entry into the table" number
3. A "gapless" series of numbers.
4. Editable numbers.
5. A series of (necessarily) always increasing numbers.
6. Intended to be viewed/used by end users of the application.
7. Predictable (as to what the previous or next one in the table is/or will be).
8. Reassigned, once deleted or discarded
9. A predictor/indicator of the number of rows in a table.
10. Intended to be used to "rank" or "sort" or "number" rows returned from the table.
11. Necessarily used to determine the default order the rows may be returned from the table.
12. Indicative of or related to any TimeStamp field that may also be in the table row.

What they are:
1. Unique numbers used to identify individual rows in a table.
2. Automatically created by Access when a new row is "instanced" by Access.
3. Great/Outstanding/Essential for use as the Primary Key of a table.
4. Great/Outstanding/Essential for use as "link points" by Foreign Keys in other tables.
5. Unchanging, once assigned to a particular table row

 

[arnelgp]

however one missed the seriality in entering.

Autonumber can easily be "missed", when a users enters a New record and start typing and eventually bail out and discontinue adding the record.
it is not similar to a Cheque number where a missing 1 is very impotant.
what matters is that our teeth should not be gapped!

 

[xavier.batlle]

I want to find all the missing IDs in a table starting from 1 to the end of the Table. How can i do that and display the missing IDs
IDs are Primary and serially numbered. however one missed the seriality in entering.

A simple way to do it would be:
Create a table with all the Id possibles (for example in Excel and attach that table)
Create a query with not matching records as @ebs17 has posted.

 

[GPGeorge]

Piling on a bit, I know.

If gaps in a sequence of AutoNumbers is a problem, you've been doing it wrong.

I love the analogy to teeth, btw. Just a couple of weeks ago, I had to have some teeth extracted. The temporary gaps are a hassle, but I know exactly where they are.

There are multiple gaps in AutoNumbers (or Identity values in SQL Server tables) in every database I have, but I have no idea where the gaps are and what values have been lost. And I don't even care!

 

[gemma-the-husky]

Just write a quick VBA function to iterate the numbers and report the gaps.

You shouldn't expect an intact sequence with an autonumber. If you want an an intact sequence you need to manage the numbering yourself.

 

[Pat Hartman]

If you need to avoid gaps, you need to start by preventing deletes. Then you need to generate your own sequence numbers. Assign them as the last line of code in the form's BeforeUpdate event and use a loop to capture duplicates in case another user is trying to insert records at the same time and you have a conflict.

 

[sbaud2003]

Thanks to all for your advise