Need to query missing numbers in list

  • Thread starter Thread starter Heather
  • Start date Start date
H

Heather

Good morning,

I have a list of employees, including manually generated
ID #'s.

Is there a way to query the table so that it gives me a
list of numbers missing from the sequence?

THanks for your assistance!
hjh
 
Here's how, one step at a time.

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
 
Good heavens, that was FAST. <g> Thanks so much for your
help! That's amazing .......
hjh
 
Dear Heather:

Well, if your post arrives just as I look to see what's new, that can
happen. If you'll coordinate your schedule with mine, I'm sure we can
get you rapid responses in the future.

This is a question that gets asked every month or so. I didn't see
anybody else answering it, so I worked out something for it. I try to
make it more complete, and do a little more "teaching" on it every
time I answer it.

Were you able to understand the step-by-step approach to the problem?
Did that help make sense of it? Were you able to modify it to your
specific situation and get your results from it? Is there anything
about it that still needs explanation?

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