Re: finds gaps in sequential numbers?

Tech-Archive recommends: Speed Up your PC by fixing your registry

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" <Pipn@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:4D0594B5-C8D7-4CDB-90B4-64954ECE398F@xxxxxxxxxxxxxxxx
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 are:
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" <Pipn@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:4B40ACAA-F79D-447D-998D-62C89036AD3D@xxxxxxxxxxxxxxxx
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" <JenniferDakCo@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message
news:52B35248-FDDA-4C7E-84EB-C498470481C6@xxxxxxxxxxxxxxxx
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" <Jennifer@DakCo@discussions.microsoft.com> wrote
in
message
news:0D212AD0-D563-46B6-AE65-006E2A5A0CC1@xxxxxxxxxxxxxxxx
How do I create a query, in MS Office Access 2000, that will find
gaps
in
manually typed sequential numbers?











.

Relevant Pages

  • Re: finds gaps in sequential numbers?
    ... of missing work orders I need to list all missing work orders before I create ... and Run a count+1 on the work order numbers. ... invoiceNo in my sequence. ... "Tom Ellison" wrote: ...
    (microsoft.public.access.queries)
  • Re: finds gaps in sequential numbers?
    ... "Tom Ellison" wrote: ... of the deleted Work Order to the next one that is saved? ... of missing work orders I need to list all missing work orders before I ... invoiceNo in my sequence. ...
    (microsoft.public.access.queries)
  • Applying a filter based on a previous selection
    ... which just stores business names. ... Is this something that can be done within the work order ... It seems as though there is something really simple that I'm missing. ... Any advice would be much appreciated. ...
    (microsoft.public.access.queries)
  • Re: finds gaps in sequential numbers?
    ... I too am looking for a way to determine missing numbers in a sequence. ... "Tom Ellison" wrote: ... This allows the query to look at the ...
    (microsoft.public.access.queries)
  • Re: finds gaps in sequential numbers?
    ... find a Number where the next number is missing, ... This allows the query to look at the ... "Tom Ellison" wrote: ... sequence of missing numbers, where X is the next larger of ...
    (microsoft.public.access.queries)