selectionquery

  • Thread starter Thread starter vdf
  • Start date Start date
V

vdf

Hi, i have a table wich has an autonumber-field, so all records ge
automatic a number.
Now, for some reasons it is possible that a record is deleted, so ther
is one number missing (so all numbers follow upon eachother exept on
(or more sometimes).
For example:
1,2,3,5,6
So number 4 is missing.
I want to make a query that finds these missing numbers (4 in thi
case) is that possible?
So the query needs to compare all numbers and picks out the ones tha
are gone, but i do not know if this is possible???

Thanks in advance
 
You could make a table called tblNumbers with records 1
through the highest number you expect the table in
question might be. Then do a query, using the two number
as the link, that will display records that do not match
between the two tables.

Marty
 
Hi, i have a table wich has an autonumber-field, so all records get
automatic a number.
Now, for some reasons it is possible that a record is deleted, so there
is one number missing (so all numbers follow upon eachother exept one
(or more sometimes).
For example:
1,2,3,5,6
So number 4 is missing.
I want to make a query that finds these missing numbers (4 in this
case) is that possible?
So the query needs to compare all numbers and picks out the ones that
are gone, but i do not know if this is possible???
Click to expand...

Why?

Autonumbers have one purpose and one purpose ONLY: to create an almost
guaranteed unique ID. They are not guaranteed to be free of gaps; they
cannot be edited; you cannot type in a new record to fill in a gap
(though you can run an Append query appending a Long Integer value
into the table).

If you want sequential, gapless numbers, DON'T use an Autonumber!
Instead, roll your own using VBA code. There are many threads in this
newsgroup discussing "Custom Counters" or such terms; do a Google
Groups search for some examples.

To directly answer your question... yes, you can find the gaps here.
Let's say your table is named MyTable and the autonumber field is
named ID. Create a self join query in the SQL window:

SELECT mytable.ID + 1 AS Missing
FROM mytable LEFT JOIN mytable AS X
WHERE X.ID = mytable.ID + 1
AND X.ID IS NULL;

Change ID to the name of your autonumber field, and mytable to the
name of your table; copy the result into the SQL window of a new query
and run it - you'll see the first ID in each "hole" (if you have
entries 1, 2, 3, 10, 11 the query will only show you 4, not the other
missing values).

Note that - as discussed - you won't really be able to DO much with
this information since you can't edit autonumbers and shouldn't try!
 
Back
Top