Missing Numbers

  • Thread starter Thread starter AHopper
  • Start date Start date
A

AHopper

I would like to find missing numbers and then print or
display the results. It is alright for numbers to be
entered more than once but numbers should not be missing.

Table: Products
ItemNumbers
468800
468801
468801
468805
468806
468807
468807
468809
468810

Missing Numbers 468802, 468803, 468804, and 468808

Thank you in advance for your help.

Allan
 
Dear Allan:

SELECT ItemNumber + 1 AS BeginSeries,
(SELECT MIN(ItemNumber) - 1 FROM Products T1
WHERE T1.ItemNumber > T.ItemNumber) AS EndSeries
FROM (SELECT DISTINCT ItemNumber FROM Products) T
WHERE NOT EXISTS (SELECT * FROM Products T1
WHERE T1.ItemNumber = T.ItemNumber + 1)
AND ItemNumber < (SELECT MAX(ItemNumber) FROM Products)

This will give you one row for your sample data, showing BeginSeries =
468802 and EndSeries = 468804, meaning numbers 468802 through 468804
are missing. Close enough?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Tom,
It is very important that no numbers be missing, so I
would like to include the 468808 if possible. How can I
know that 468808 is also missing?
If I have the user choose the start and end of the series
on a form how would I incorporate that? Would this allow
468808 to be included?
Form "ItemNumberInquiry"
Combo boxes on form "StartOfSeries" and "EndOfSeries".

I haven't used Queries other than those created by Access
very much so I am having some difficulty understanding how
the query works.
Are Products T1 and FROM Products) T different Tables?
Are they variables that I need to declare?

What is the best way or a way to show the end user the
results (report, on screen)? Can I print the results?

Thank you very much for your response and help.

Allan
 
Dear Allan:

See below:

Tom,
It is very important that no numbers be missing, so I
would like to include the 468808 if possible. How can I
know that 468808 is also missing?
Click to expand...

The query I provided should be read as reporting that ALL the numbers
from 468802 through 468804 are missing. That is meant to include
468803. It is a range. When only one consecutive number is missing,
you will have a range like from 999999 through 999999. When a hundred
consecutive rows are missing, you will get only one Start/End pair for
that.

In order to display every number missing in its own row of the table
you would probably need a table of all possible numbers. Given the
size of your numbers, this would be a huge table and the query may be
quite slow. Because of my experience with the missing number problem,
I recommend you try to use something of the form I showed you.
If I have the user choose the start and end of the series
on a form how would I incorporate that? Would this allow
468808 to be included?
Click to expand...

I suggest you modify the subquery:

FROM (SELECT DISTINCT ItemNumber FROM Products) T

to read:

FROM (SELECT DISTINCT ItemNumber FROM Products
WHERE ItemNumber BETWEEN [StartOfSeries] AND [EndOfSeries]) T

and also change

AND ItemNumber < (SELECT MAX(ItemNumber) FROM Products)

to read

AND ItemNumber < (SELECT MAX(ItemNumber) FROM Products)
AND ItemNumber <= [EndOfSeries]


Form "ItemNumberInquiry"
Combo boxes on form "StartOfSeries" and "EndOfSeries".

I haven't used Queries other than those created by Access
very much so I am having some difficulty understanding how
the query works.
Are Products T1 and FROM Products) T different Tables?
Click to expand...

No, T and T1 are aliases for two instances of the same table.
Are they variables that I need to declare?
Click to expand...

No. I wrote and tested the query here exactly as I gave it. It is
complete and works.
What is the best way or a way to show the end user the
results (report, on screen)? Can I print the results?
Click to expand...

As a query it can be shown on a form, in a report, put into a
spreadsheet, etc.
Thank you very much for your response and help.

Allan
Click to expand...

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Tom,
My ignorance is showing. I am doing something wrong. I
opened a new query and put in the SQL statement in it's
original form with no modifications. When I try to open
the query I get the following message:
"The Microsoft Jet database engine cannot find the input
table or query 'T1'. Make sure it exists and that its name
is spelled correctly."

Thanks again for your patience and time.
Allan
-----Original Message-----
Dear Allan:

See below:

Tom,
It is very important that no numbers be missing, so I
would like to include the 468808 if possible. How can I
know that 468808 is also missing?
Click to expand...

The query I provided should be read as reporting that ALL the numbers
from 468802 through 468804 are missing. That is meant to include
468803. It is a range. When only one consecutive number is missing,
you will have a range like from 999999 through 999999. When a hundred
consecutive rows are missing, you will get only one Start/End pair for
that.

In order to display every number missing in its own row of the table
you would probably need a table of all possible numbers. Given the
size of your numbers, this would be a huge table and the query may be
quite slow. Because of my experience with the missing number problem,
I recommend you try to use something of the form I showed you.
If I have the user choose the start and end of the series
on a form how would I incorporate that? Would this allow
468808 to be included?
Click to expand...

I suggest you modify the subquery:

FROM (SELECT DISTINCT ItemNumber FROM Products) T

to read:

FROM (SELECT DISTINCT ItemNumber FROM Products
WHERE ItemNumber BETWEEN [StartOfSeries] AND [EndOfSeries]) T

and also change

AND ItemNumber < (SELECT MAX(ItemNumber) FROM Products)

to read

AND ItemNumber < (SELECT MAX(ItemNumber) FROM Products)
AND ItemNumber <= [EndOfSeries]


Form "ItemNumberInquiry"
Combo boxes on form "StartOfSeries" and "EndOfSeries".

I haven't used Queries other than those created by Access
very much so I am having some difficulty understanding how
the query works.
Are Products T1 and FROM Products) T different Tables?
Click to expand...

No, T and T1 are aliases for two instances of the same table.
Are they variables that I need to declare?
Click to expand...

No. I wrote and tested the query here exactly as I gave it. It is
complete and works.
What is the best way or a way to show the end user the
results (report, on screen)? Can I print the results?
Click to expand...

As a query it can be shown on a form, in a report, put into a
spreadsheet, etc.
Thank you very much for your response and help.

Allan
showing
BeginSeries =
Click to expand...

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
.
Click to expand...
 
Dear Allan:

The query is tested and working here. Perhaps you should try it
again, pasting it in from my post. Check the column and table names I
used carefully.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Tom
Do I have to add instances of the table to the query and
then use Products1 for Products T1, Products1 for T1,
Products for T?

I do have other fields in the table "Products", does that
make a difference? For example I have a field JobNumber
that I could use to narrow the ItemNumbers to one job.

Allan
 
Dear Allan:

You don't need to add anything to the query at this point. It is
self-contained and complete. The SQL text tells everything.

If you have other columns you need to see or use in the results, then
you may have to add them. But, for the results you describe (just
seeing missing numbers) you don't need anything. I already eliminated
duplicate ItemNumbers using a DISTINCT subquery in the FROM clause.

Are you still having the original problem, where it complains about
T1? Please post back the SQL of what you have now so I can see that.

It shouldn't be that difficult (not meant as an insult to you, just
puzzling about what is happening). If I have the table and column
names correct, it should just work.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Tom
Sorry to take so much of your time. You were right. I did
get the query to work as you said, however, my computer
froze. I didn't think about the number of records in the
table (38,789)which is probably much too big.
I can make the range smaller by using the "StartOfSeries"
and "EndOfSeries" on the "ItemNumberInquiry" Form.
The "Products" table also has the "JobNumber" field which
I mentioned before. I could include a combo box on
the "ItemNumberInquiry" Form for "JobNumber". I would then
be able to search limit the search to one job and a range
within that job. How would I use "JobNumber" with the
query?

Allan
 
Dear Allan:

SELECT ItemNumber + 1 AS BeginSeries,
(SELECT MIN(ItemNumber) - 1 FROM Products T1
WHERE T1.ItemNumber > T.ItemNumber) AS EndSeries
FROM (SELECT DISTINCT ItemNumber, JobNumber FROM Products
WHERE JobNumber = Forms!FormName!ComboBox) T
WHERE NOT EXISTS (SELECT * FROM Products T1
WHERE T1.ItemNumber = T.ItemNumber + 1)
AND ItemNumber < (SELECT MAX(ItemNumber) FROM Products)

Substitute the actual name of your form and combo box.

This kind of query is not fast in Jet. I changed to working only in
MSDE more than 3 years ago because it provides many benefits, one of
them performance. So, how long did it take to run on almost 40K rows
of data?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Tom,
In order to answer your question I ran the query again. It
took 3 and a half minutes. The results show on the screen
but if I try to move down the list my computer freezes.

This has been a new step for me. I don't think I will be
able to finish all that needs to be done tonight (create
form, with JobNumber, StartOfSeries, and EndOfSeries). I
will try to follow your instructions and bring it all
together. I will post again if I have problems. Hope you
will see the post. I will use "Missing Numbers" again as
the subject.

I genuinely want to thank you for your help.
Allan
 
Dear Allan:

Thanks for the statistics.

Changing the table and column names, I ran this on a database here
with 2300 rows. It found more than 200 sequences of gaps in 0.2
seconds. Your database is 20 times this size, so it might take about
4 seconds.

The ratio of 50:1 or so is not untypical of what I have experienced by
using MSDE instead of Jet. And this is without performing any
optimization. The tools that are used in developing for MSDE have
ways of detecting and displaying things you can do to improve this
performance. It is often possible to improve the performance by a
factor of 5 or 10 yet again.

Then, there are advanced techniques such as puting all the numbers
(ignoring other columns) into a temporary table (which will reside in
memory), indexing it, and running the query with all its subqueries
against that. Under that condition, I really expect you would have
your results out of the 40K rows in a second or two. (It's hard to
believe this until you've done it a few times.) There can be an
improvement in performance of a thousand to one in some cases.

When you get serious about using some of the more powerful features of
SQL, you may find (as I have) that moving to MSDE is indispensable.

If your databases get really large, with millions of rows, you will
find you cannot even get the development done when each test requires
hours to run. You spend time developing ways to allow yourself to
develop, and then hoping this extends to the finished product
correctly.

I currently have fairly well optimized queries that run for half a
minute or so in MSDE. As these are highly optimized, this might
equate to several hours with Jet (and I've been there, too! I cut my
teeth on Access with Jet for 3 years already.)

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