Finding missing keys

  • Thread starter Thread starter mgp
  • Start date Start date
M

mgp

How do I querry a table that has a 5 digit number as the
key to determine if there are any numbers missing?

For example: My table "orders" currently has 1118 records
with a key starting with 74801 and finishes with 76209.
There are some keys that were either deleted or skipped
when inserting records. How can I find out which numbers
are missing in the table so that I can verify table
integrity?

a2k
 
Here is an example I copied from MVP Tom Ellison

SELECT AccountNumber
FROM YourTable T
WHERE NOT EXISTS (SELECT * FROM YourTable T1
WHERE T1.AccountNumber = T.AccountNumber + 1)

This finds every account where the next number doesn't exist.
However, the last (highest) account number probably isn't to be
considered, so remove that:

SELECT AccountNumber
FROM YourTable T
WHERE NOT EXISTS (SELECT * FROM YourTable T1
WHERE T1.AccountNumber = T.AccountNumber + 1)
AND AccountNumber <> (SELECT MAX(AccountNumber) FROM YourTable)

This is showing the existing AccountNumbers for which the next
consecutive number is missing. Each time you have this, there is a
range of missing account numbers following, beginning with that
AccountNumber plus 1. Next, we will find the end of the range(s):

SELECT AccountNumber + 1 AS BeginRange,
(SELECT MIN(AccountNumber) - 1 FROM YourTable T1
WHERE T1.AccountNumber > T.AccountNumber) AS EndRange
FROM YourTable T
WHERE NOT EXISTS (SELECT * FROM YourTable T1
WHERE T1.AccountNumber = T.AccountNumber + 1)
AND AccountNumber <> (SELECT MAX(AccountNumber) FROM YourTable)

The added code is a subquery which finds the next existing
AccountNumber after the series of missing numbers.

You will need to substitute your table and column names in the above.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Yep, that ought to do it.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Works like a charm .. thx John and Tom!
-----Original Message-----
Yep, that ought to do it.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts



.
Click to expand...
 
Back
Top