finds gaps in sequential numbers?

How do I create a query, in MS Office Access 2000, that will find gaps in
manually typed sequential numbers?

Dear Jennifer:

I would do this with a subquery that checks for N + 1 not existing as the
start of a sequence of missing numbers, and X - 1 as the end of that
sequence of missing numbers, where X is the next larger (the MIN() of those
greater than).

SELECT Number + 1 AS BeginMissing,
(SELECT MIN(Number)
FROM YourTable T1
WHERE T1.Number T.Number) - 1
AS EndMissing
FROM YourTable T
WHERE NOT EXISTS (
SELECT * FROM YourTable T1
WHERE T1.Number = T.Number + 1)
AND Number
(SELECT MAX(Number) FROM YourTable)

Substitute the actual name of YourTable and the column name having the
Number in it.

This will work much more quickly if there is an index on the column I called
Number.

Would you benefit from me breaking this down and explaining how it works?

Tom Ellison


If you post the name of the table and of the column having these numbers, I
could create the actual SQL.
"Jennifer@DakCo" wrote in message
...
How do I create a query, in MS Office Access 2000, that will find gaps in
manually typed sequential numbers?

Tom - thanks so much for the info! I spent a lot of time trying to find a
solution for this without much success until you responded to my post.

And yes, I think it would benefit me if you could break this down and
explain how it works.

Jennifer

"Tom Ellison" wrote:

Dear Jennifer:

I would do this with a subquery that checks for N + 1 not existing as the
start of a sequence of missing numbers, and X - 1 as the end of that
sequence of missing numbers, where X is the next larger (the MIN() of those
greater than).

SELECT Number + 1 AS BeginMissing,
(SELECT MIN(Number)
FROM YourTable T1
WHERE T1.Number T.Number) - 1
AS EndMissing
FROM YourTable T
WHERE NOT EXISTS (
SELECT * FROM YourTable T1
WHERE T1.Number = T.Number + 1)
AND Number
(SELECT MAX(Number) FROM YourTable)

Substitute the actual name of YourTable and the column name having the
Number in it.

This will work much more quickly if there is an index on the column I called
Number.

Would you benefit from me breaking this down and explaining how it works?

Tom Ellison


If you post the name of the table and of the column having these numbers, I
could create the actual SQL.
"Jennifer@DakCo" wrote in message
...
How do I create a query, in MS Office Access 2000, that will find gaps in
manually typed sequential numbers?

Dear Jennifer:

Step 1: find a Number where the next number is missing, but not the
greatest number in the table:

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

Here, a subquery in the EXISTS clause looks for Number + 1.

SELECT * FROM YourTable T1
WHERE T1.Number = T.Number + 1

Notice the use of Aliases T and T1. This allows the query to look at the
whole table using T1 without referenced to the "outer" query based on an
instance of the table called T. The WHERE clause relates the two copies of
the table. It says, "look for the next sequential value in Number, relative
to the value in the outer query which is considering each value of Number
that exists."

Another subquery:

SELECT MAX(Number) FROM YourTable

Simply returns the largest value of Number in the table. We want to omit
that value from our list of missing ranges.

Now we have the values of Number that are just before the range of missing
numbers. Add 1 to define the start of each range. Now, what is the upper
end of each range? To find that, find the next larger Number that does
exist and subtract 1:

(SELECT MIN(Number)
FROM YourTable T1
WHERE T1.Number T.Number) - 1

That's it! Any clearer? I would think it reads well, but you would
probably need to practice it. These techniques have a wide range of
applications.

Read up on the subjects "alias" and "subquery".

Tom Ellison


"Jennifer@DakCo" wrote in message
...
Tom - thanks so much for the info! I spent a lot of time trying to find a
solution for this without much success until you responded to my post.

And yes, I think it would benefit me if you could break this down and
explain how it works.

Jennifer

"Tom Ellison" wrote:

Dear Jennifer:

I would do this with a subquery that checks for N + 1 not existing as the
start of a sequence of missing numbers, and X - 1 as the end of that
sequence of missing numbers, where X is the next larger (the MIN() of
those
greater than).

SELECT Number + 1 AS BeginMissing,
(SELECT MIN(Number)
FROM YourTable T1
WHERE T1.Number T.Number) - 1
AS EndMissing
FROM YourTable T
WHERE NOT EXISTS (
SELECT * FROM YourTable T1
WHERE T1.Number = T.Number + 1)
AND Number
(SELECT MAX(Number) FROM YourTable)

Substitute the actual name of YourTable and the column name having the
Number in it.

This will work much more quickly if there is an index on the column I
called
Number.

Would you benefit from me breaking this down and explaining how it works?

Tom Ellison


If you post the name of the table and of the column having these numbers,
I
could create the actual SQL.
"Jennifer@DakCo" wrote in
message
...
How do I create a query, in MS Office Access 2000, that will find gaps
in
manually typed sequential numbers?

Thanks again Tom. I definitely will practice this but I think I can follow
it. I know you have saved me a great deal of time and frustration with your
response.

Jennifer

"Tom Ellison" wrote:

Dear Jennifer:

Step 1: find a Number where the next number is missing, but not the
greatest number in the table:

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

Here, a subquery in the EXISTS clause looks for Number + 1.

SELECT * FROM YourTable T1
WHERE T1.Number = T.Number + 1

Notice the use of Aliases T and T1. This allows the query to look at the
whole table using T1 without referenced to the "outer" query based on an
instance of the table called T. The WHERE clause relates the two copies of
the table. It says, "look for the next sequential value in Number, relative
to the value in the outer query which is considering each value of Number
that exists."

Another subquery:

SELECT MAX(Number) FROM YourTable

Simply returns the largest value of Number in the table. We want to omit
that value from our list of missing ranges.

Now we have the values of Number that are just before the range of missing
numbers. Add 1 to define the start of each range. Now, what is the upper
end of each range? To find that, find the next larger Number that does
exist and subtract 1:

(SELECT MIN(Number)
FROM YourTable T1
WHERE T1.Number T.Number) - 1

That's it! Any clearer? I would think it reads well, but you would
probably need to practice it. These techniques have a wide range of
applications.

Read up on the subjects "alias" and "subquery".

Tom Ellison


"Jennifer@DakCo" wrote in message
...
Tom - thanks so much for the info! I spent a lot of time trying to find a
solution for this without much success until you responded to my post.

And yes, I think it would benefit me if you could break this down and
explain how it works.

Jennifer

"Tom Ellison" wrote:

Dear Jennifer:

I would do this with a subquery that checks for N + 1 not existing as the
start of a sequence of missing numbers, and X - 1 as the end of that
sequence of missing numbers, where X is the next larger (the MIN() of
those
greater than).

SELECT Number + 1 AS BeginMissing,
(SELECT MIN(Number)
FROM YourTable T1
WHERE T1.Number T.Number) - 1
AS EndMissing
FROM YourTable T
WHERE NOT EXISTS (
SELECT * FROM YourTable T1
WHERE T1.Number = T.Number + 1)
AND Number
(SELECT MAX(Number) FROM YourTable)

Substitute the actual name of YourTable and the column name having the
Number in it.

This will work much more quickly if there is an index on the column I
called
Number.

Would you benefit from me breaking this down and explaining how it works?

Tom Ellison


If you post the name of the table and of the column having these numbers,
I
could create the actual SQL.
"Jennifer@DakCo" wrote in
message
...
How do I create a query, in MS Office Access 2000, that will find gaps
in
manually typed sequential numbers?

I too am looking for a way to determine missing numbers in a sequence.

Your solution is a wonderful way to determine the ranges missing. Is there a
way to list each missing number through this solution?

Pip''n


"Tom Ellison" wrote:

Dear Jennifer:

Step 1: find a Number where the next number is missing, but not the
greatest number in the table:

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

Here, a subquery in the EXISTS clause looks for Number + 1.

SELECT * FROM YourTable T1
WHERE T1.Number = T.Number + 1

Notice the use of Aliases T and T1. This allows the query to look at the
whole table using T1 without referenced to the "outer" query based on an
instance of the table called T. The WHERE clause relates the two copies of
the table. It says, "look for the next sequential value in Number, relative
to the value in the outer query which is considering each value of Number
that exists."

Another subquery:

SELECT MAX(Number) FROM YourTable

Simply returns the largest value of Number in the table. We want to omit
that value from our list of missing ranges.

Now we have the values of Number that are just before the range of missing
numbers. Add 1 to define the start of each range. Now, what is the upper
end of each range? To find that, find the next larger Number that does
exist and subtract 1:

(SELECT MIN(Number)
FROM YourTable T1
WHERE T1.Number T.Number) - 1

That's it! Any clearer? I would think it reads well, but you would
probably need to practice it. These techniques have a wide range of
applications.

Read up on the subjects "alias" and "subquery".

Tom Ellison


"Jennifer@DakCo" wrote in message
...
Tom - thanks so much for the info! I spent a lot of time trying to find a
solution for this without much success until you responded to my post.

And yes, I think it would benefit me if you could break this down and
explain how it works.

Jennifer

"Tom Ellison" wrote:

Dear Jennifer:

I would do this with a subquery that checks for N + 1 not existing as the
start of a sequence of missing numbers, and X - 1 as the end of that
sequence of missing numbers, where X is the next larger (the MIN() of
those
greater than).

SELECT Number + 1 AS BeginMissing,
(SELECT MIN(Number)
FROM YourTable T1
WHERE T1.Number T.Number) - 1
AS EndMissing
FROM YourTable T
WHERE NOT EXISTS (
SELECT * FROM YourTable T1
WHERE T1.Number = T.Number + 1)
AND Number
(SELECT MAX(Number) FROM YourTable)

Substitute the actual name of YourTable and the column name having the
Number in it.

This will work much more quickly if there is an index on the column I
called
Number.

Would you benefit from me breaking this down and explaining how it works?

Tom Ellison


If you post the name of the table and of the column having these numbers,
I
could create the actual SQL.
"Jennifer@DakCo" wrote in
message
...
How do I create a query, in MS Office Access 2000, that will find gaps
in
manually typed sequential numbers?

Dear Pip:

Showing all the missing numbers would be quite a different problem. You
would need a table of numbers, perhaps thousands. A LEFT JOIN or NOT EXISTS
can be made made against this list, limited by the largest value, if that's
what you desire.

Given full details, I could work out a solution on this basis, too.

Tom Ellison


"Pip''''n" wrote in message
...
I too am looking for a way to determine missing numbers in a sequence.

Your solution is a wonderful way to determine the ranges missing. Is there
a
way to list each missing number through this solution?

Pip''n


"Tom Ellison" wrote:

Dear Jennifer:

Step 1: find a Number where the next number is missing, but not the
greatest number in the table:

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

Here, a subquery in the EXISTS clause looks for Number + 1.

SELECT * FROM YourTable T1
WHERE T1.Number = T.Number + 1

Notice the use of Aliases T and T1. This allows the query to look at the
whole table using T1 without referenced to the "outer" query based on an
instance of the table called T. The WHERE clause relates the two copies
of
the table. It says, "look for the next sequential value in Number,
relative
to the value in the outer query which is considering each value of Number
that exists."

Another subquery:

SELECT MAX(Number) FROM YourTable

Simply returns the largest value of Number in the table. We want to omit
that value from our list of missing ranges.

Now we have the values of Number that are just before the range of
missing
numbers. Add 1 to define the start of each range. Now, what is the
upper
end of each range? To find that, find the next larger Number that does
exist and subtract 1:

(SELECT MIN(Number)
FROM YourTable T1
WHERE T1.Number T.Number) - 1

That's it! Any clearer? I would think it reads well, but you would
probably need to practice it. These techniques have a wide range of
applications.

Read up on the subjects "alias" and "subquery".

Tom Ellison


"Jennifer@DakCo" wrote in
message
...
Tom - thanks so much for the info! I spent a lot of time trying to
find a
solution for this without much success until you responded to my post.

And yes, I think it would benefit me if you could break this down and
explain how it works.

Jennifer

"Tom Ellison" wrote:

Dear Jennifer:

I would do this with a subquery that checks for N + 1 not existing as
the
start of a sequence of missing numbers, and X - 1 as the end of that
sequence of missing numbers, where X is the next larger (the MIN() of
those
greater than).

SELECT Number + 1 AS BeginMissing,
(SELECT MIN(Number)
FROM YourTable T1
WHERE T1.Number T.Number) - 1
AS EndMissing
FROM YourTable T
WHERE NOT EXISTS (
SELECT * FROM YourTable T1
WHERE T1.Number = T.Number + 1)
AND Number
(SELECT MAX(Number) FROM YourTable)

Substitute the actual name of YourTable and the column name having the
Number in it.

This will work much more quickly if there is an index on the column I
called
Number.

Would you benefit from me breaking this down and explaining how it
works?

Tom Ellison


If you post the name of the table and of the column having these
numbers,
I
could create the actual SQL.
"Jennifer@DakCo" wrote in
message
...
How do I create a query, in MS Office Access 2000, that will find
gaps
in
manually typed sequential numbers?

Dear Tom:

Currently I have a listing of [Work Order]s in a table called [Work Orders]
i know the naming isn't the best but it was before I learned what I know now.
This listing is used to create a bill each month. To prevent the possibility
of missing work orders I need to list all missing work orders before I create
the bill. Missing orders are then entered into the system.

Currently I have a form to perform this task. I began by storing the min
work order for the location. and Run a count+1 on the work order numbers.
Opening a form to the desired record each time. If the record can't be found
the number is concatenated to a string that is returned once the maximum work
order is reached. Each time the code is run, the first missing work order - 1
is stored as the minimum work order for that location.

The values stored a
MinROMissed : starting value
MaxRO : last entered work order
[Current Invoice] : stores current invoice being checked
[Location ID] : each store has a different range of work orders (values 1,2,3)

It's a crude way of doing it, but it works. I'd just like to make it better.

*********************************
In a separate system, I need to assign an InvoiceNo to a record upon
completion of a sale. Currently I assign the maximum number + 1 but i know
this could create problems if a number is assigned when opening this form, a
second is opened and the first is cancelled. I would end up missing an
invoiceNo in my sequence.

Your assistance is greatly appreciated,
Thanks in advance,

Pip'n

"Tom Ellison" wrote:

Dear Pip:

Showing all the missing numbers would be quite a different problem. You
would need a table of numbers, perhaps thousands. A LEFT JOIN or NOT EXISTS
can be made made against this list, limited by the largest value, if that's
what you desire.

Given full details, I could work out a solution on this basis, too.

Tom Ellison


"Pip''''n" wrote in message
...
I too am looking for a way to determine missing numbers in a sequence.

Your solution is a wonderful way to determine the ranges missing. Is there
a
way to list each missing number through this solution?

Pip''n


"Tom Ellison" wrote:

Dear Jennifer:

Step 1: find a Number where the next number is missing, but not the
greatest number in the table:

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

Here, a subquery in the EXISTS clause looks for Number + 1.

SELECT * FROM YourTable T1
WHERE T1.Number = T.Number + 1

Notice the use of Aliases T and T1. This allows the query to look at the
whole table using T1 without referenced to the "outer" query based on an
instance of the table called T. The WHERE clause relates the two copies
of
the table. It says, "look for the next sequential value in Number,
relative
to the value in the outer query which is considering each value of Number
that exists."

Another subquery:

SELECT MAX(Number) FROM YourTable

Simply returns the largest value of Number in the table. We want to omit
that value from our list of missing ranges.

Now we have the values of Number that are just before the range of
missing
numbers. Add 1 to define the start of each range. Now, what is the
upper
end of each range? To find that, find the next larger Number that does
exist and subtract 1:

(SELECT MIN(Number)
FROM YourTable T1
WHERE T1.Number T.Number) - 1

That's it! Any clearer? I would think it reads well, but you would
probably need to practice it. These techniques have a wide range of
applications.

Read up on the subjects "alias" and "subquery".

Tom Ellison


"Jennifer@DakCo" wrote in
message
...
Tom - thanks so much for the info! I spent a lot of time trying to
find a
solution for this without much success until you responded to my post.

And yes, I think it would benefit me if you could break this down and
explain how it works.

Jennifer

"Tom Ellison" wrote:

Dear Jennifer:

I would do this with a subquery that checks for N + 1 not existing as
the
start of a sequence of missing numbers, and X - 1 as the end of that
sequence of missing numbers, where X is the next larger (the MIN() of
those
greater than).

SELECT Number + 1 AS BeginMissing,
(SELECT MIN(Number)
FROM YourTable T1
WHERE T1.Number T.Number) - 1
AS EndMissing
FROM YourTable T
WHERE NOT EXISTS (
SELECT * FROM YourTable T1
WHERE T1.Number = T.Number + 1)
AND Number
(SELECT MAX(Number) FROM YourTable)

Substitute the actual name of YourTable and the column name having the
Number in it.

This will work much more quickly if there is an index on the column I
called
Number.

Would you benefit from me breaking this down and explaining how it
works?

Tom Ellison


If you post the name of the table and of the column having these
numbers,
I
could create the actual SQL.
"Jennifer@DakCo" wrote in
message
...
How do I create a query, in MS Office Access 2000, that will find
gaps
in
manually typed sequential numbers?

Dear Pip:

Are Work Orders ever deleted? If they are, do you want to assign the number
of the deleted Work Order to the next one that is saved?

Perhaps it would be good to wait to assign the new Work Order numbers until
the BeforeUpdate event. Test for NewRecord, and if true, set the Work Order
Number at that time. Use the MAX() of the current work orders.

In a busy multi-user system, it might also need some locking.

I'm really thinking you don't want to fill in the gaps created by deletions.
Perhaps you would consider not deleting such work orders, but just marking
them. Depends on may factors of the design.

Tom Ellison


"Pip''''n" wrote in message
...
Dear Tom:

Currently I have a listing of [Work Order]s in a table called [Work
Orders]
i know the naming isn't the best but it was before I learned what I know
now.
This listing is used to create a bill each month. To prevent the
possibility
of missing work orders I need to list all missing work orders before I
create
the bill. Missing orders are then entered into the system.

Currently I have a form to perform this task. I began by storing the min
work order for the location. and Run a count+1 on the work order numbers.
Opening a form to the desired record each time. If the record can't be
found
the number is concatenated to a string that is returned once the maximum
work
order is reached. Each time the code is run, the first missing work
order - 1
is stored as the minimum work order for that location.

The values stored a
MinROMissed : starting value
MaxRO : last entered work order
[Current Invoice] : stores current invoice being checked
[Location ID] : each store has a different range of work orders (values
1,2,3)

It's a crude way of doing it, but it works. I'd just like to make it
better.

*********************************
In a separate system, I need to assign an InvoiceNo to a record upon
completion of a sale. Currently I assign the maximum number + 1 but i know
this could create problems if a number is assigned when opening this form,
a
second is opened and the first is cancelled. I would end up missing an
invoiceNo in my sequence.

Your assistance is greatly appreciated,
Thanks in advance,

Pip'n

"Tom Ellison" wrote:

Dear Pip:

Showing all the missing numbers would be quite a different problem. You
would need a table of numbers, perhaps thousands. A LEFT JOIN or NOT
EXISTS
can be made made against this list, limited by the largest value, if
that's
what you desire.

Given full details, I could work out a solution on this basis, too.

Tom Ellison


"Pip''''n" wrote in message
...
I too am looking for a way to determine missing numbers in a sequence.

Your solution is a wonderful way to determine the ranges missing. Is
there
a
way to list each missing number through this solution?

Pip''n


"Tom Ellison" wrote:

Dear Jennifer:

Step 1: find a Number where the next number is missing, but not the
greatest number in the table:

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

Here, a subquery in the EXISTS clause looks for Number + 1.

SELECT * FROM YourTable T1
WHERE T1.Number = T.Number + 1

Notice the use of Aliases T and T1. This allows the query to look at
the
whole table using T1 without referenced to the "outer" query based on
an
instance of the table called T. The WHERE clause relates the two
copies
of
the table. It says, "look for the next sequential value in Number,
relative
to the value in the outer query which is considering each value of
Number
that exists."

Another subquery:

SELECT MAX(Number) FROM YourTable

Simply returns the largest value of Number in the table. We want to
omit
that value from our list of missing ranges.

Now we have the values of Number that are just before the range of
missing
numbers. Add 1 to define the start of each range. Now, what is the
upper
end of each range? To find that, find the next larger Number that
does
exist and subtract 1:

(SELECT MIN(Number)
FROM YourTable T1
WHERE T1.Number T.Number) - 1

That's it! Any clearer? I would think it reads well, but you would
probably need to practice it. These techniques have a wide range of
applications.

Read up on the subjects "alias" and "subquery".

Tom Ellison


"Jennifer@DakCo" wrote in
message
...
Tom - thanks so much for the info! I spent a lot of time trying to
find a
solution for this without much success until you responded to my
post.

And yes, I think it would benefit me if you could break this down
and
explain how it works.

Jennifer

"Tom Ellison" wrote:

Dear Jennifer:

I would do this with a subquery that checks for N + 1 not existing
as
the
start of a sequence of missing numbers, and X - 1 as the end of
that
sequence of missing numbers, where X is the next larger (the MIN()
of
those
greater than).

SELECT Number + 1 AS BeginMissing,
(SELECT MIN(Number)
FROM YourTable T1
WHERE T1.Number T.Number) - 1
AS EndMissing
FROM YourTable T
WHERE NOT EXISTS (
SELECT * FROM YourTable T1
WHERE T1.Number = T.Number + 1)
AND Number
(SELECT MAX(Number) FROM YourTable)

Substitute the actual name of YourTable and the column name having
the
Number in it.

This will work much more quickly if there is an index on the column
I
called
Number.

Would you benefit from me breaking this down and explaining how it
works?

Tom Ellison


If you post the name of the table and of the column having these
numbers,
I
could create the actual SQL.
"Jennifer@DakCo" wrote
in
message
...
How do I create a query, in MS Office Access 2000, that will find
gaps
in
manually typed sequential numbers?

Dear Tom:

No, Work orders are never deleted. They are recorded manually from printouts
so the work order number is manually entered. I just need a listing of the
missing work orders to enter any that may have been missed through the manual
entry system.

This system will only ever have one user for each location.

*******************************
This second part is for a different program.

Is there an easy way to test for a new record?

These records aren't deleted after they are completed. However there are
times that the invoice is started and then canceled for some reason to be
entered later. Once the form is finished it can not be deleted.

I'll probably use the BeforeUpdate event like you mentioned. However, how do
I assign in code the maximum(InvoiceNo) to the field. Just use an update
query called from the event? I could also call it from the 'save' button or
is that not suggested?

Cheers and thanks,

Pip''n

"Tom Ellison" wrote:

Dear Pip:

Are Work Orders ever deleted? If they are, do you want to assign the number
of the deleted Work Order to the next one that is saved?

Perhaps it would be good to wait to assign the new Work Order numbers until
the BeforeUpdate event. Test for NewRecord, and if true, set the Work Order
Number at that time. Use the MAX() of the current work orders.

In a busy multi-user system, it might also need some locking.

I'm really thinking you don't want to fill in the gaps created by deletions.
Perhaps you would consider not deleting such work orders, but just marking
them. Depends on may factors of the design.

Tom Ellison


"Pip''''n" wrote in message
...
Dear Tom:

Currently I have a listing of [Work Order]s in a table called [Work
Orders]
i know the naming isn't the best but it was before I learned what I know
now.
This listing is used to create a bill each month. To prevent the
possibility
of missing work orders I need to list all missing work orders before I
create
the bill. Missing orders are then entered into the system.

Currently I have a form to perform this task. I began by storing the min
work order for the location. and Run a count+1 on the work order numbers.
Opening a form to the desired record each time. If the record can't be
found
the number is concatenated to a string that is returned once the maximum
work
order is reached. Each time the code is run, the first missing work
order - 1
is stored as the minimum work order for that location.

The values stored a
MinROMissed : starting value
MaxRO : last entered work order
[Current Invoice] : stores current invoice being checked
[Location ID] : each store has a different range of work orders (values
1,2,3)

It's a crude way of doing it, but it works. I'd just like to make it
better.

*********************************
In a separate system, I need to assign an InvoiceNo to a record upon
completion of a sale. Currently I assign the maximum number + 1 but i know
this could create problems if a number is assigned when opening this form,
a
second is opened and the first is cancelled. I would end up missing an
invoiceNo in my sequence.

Your assistance is greatly appreciated,
Thanks in advance,

Pip'n

"Tom Ellison" wrote:

Dear Pip:

Showing all the missing numbers would be quite a different problem. You
would need a table of numbers, perhaps thousands. A LEFT JOIN or NOT
EXISTS
can be made made against this list, limited by the largest value, if
that's
what you desire.

Given full details, I could work out a solution on this basis, too.

Tom Ellison


"Pip''''n" wrote in message
...
I too am looking for a way to determine missing numbers in a sequence.

Your solution is a wonderful way to determine the ranges missing. Is
there
a
way to list each missing number through this solution?

Pip''n


"Tom Ellison" wrote:

Dear Jennifer:

Step 1: find a Number where the next number is missing, but not the
greatest number in the table:

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

Here, a subquery in the EXISTS clause looks for Number + 1.

SELECT * FROM YourTable T1
WHERE T1.Number = T.Number + 1

Notice the use of Aliases T and T1. This allows the query to look at
the
whole table using T1 without referenced to the "outer" query based on
an
instance of the table called T. The WHERE clause relates the two
copies
of
the table. It says, "look for the next sequential value in Number,
relative
to the value in the outer query which is considering each value of
Number
that exists."

Another subquery:

SELECT MAX(Number) FROM YourTable

Simply returns the largest value of Number in the table. We want to
omit
that value from our list of missing ranges.

Now we have the values of Number that are just before the range of
missing
numbers. Add 1 to define the start of each range. Now, what is the
upper
end of each range? To find that, find the next larger Number that
does
exist and subtract 1:

(SELECT MIN(Number)
FROM YourTable T1
WHERE T1.Number T.Number) - 1

That's it! Any clearer? I would think it reads well, but you would
probably need to practice it. These techniques have a wide range of
applications.

Read up on the subjects "alias" and "subquery".

Tom Ellison


"Jennifer@DakCo" wrote in
message
...
Tom - thanks so much for the info! I spent a lot of time trying to
find a
solution for this without much success until you responded to my
post.

And yes, I think it would benefit me if you could break this down
and
explain how it works.

Jennifer

"Tom Ellison" wrote:

Dear Jennifer:

I would do this with a subquery that checks for N + 1 not existing
as
the
start of a sequence of missing numbers, and X - 1 as the end of
that
sequence of missing numbers, where X is the next larger (the MIN()
of
those
greater than).

SELECT Number + 1 AS BeginMissing,
(SELECT MIN(Number)
FROM YourTable T1
WHERE T1.Number T.Number) - 1
AS EndMissing
FROM YourTable T
WHERE NOT EXISTS (
SELECT * FROM YourTable T1
WHERE T1.Number = T.Number + 1)
AND Number
(SELECT MAX(Number) FROM YourTable)

Substitute the actual name of YourTable and the column name having
the
Number in it.

This will work much more quickly if there is an index on the column
I
called
Number.

Would you benefit from me breaking this down and explaining how it
works?

Tom Ellison


If you post the name of the table and of the column having these
numbers,
I
could create the actual SQL.
"Jennifer@DakCo" wrote
in
message
...
How do I create a query, in MS Office Access 2000, that will find
gaps
in
manually typed sequential numbers?