|
|
Hi all,
Is it possible to find missing numbers from a sequence? For example, if my
table data (in a column) is 1,2,3,4,6,7,8,9,11,12... the desired result
would be 5,10
Is this something that can be done? A while back, someone mentioned setting
up another table that has the complete number sequence and then do a
comparison, but is there something else that can be done?
We have some buggy web order importing going on, and I want to be able to
catch these misses so they are not forgotten.
Help is appreciated, thanks
-gary
|
|
On Wed, 26 Nov 2008 20:15:01 -0800, Gary Dolliver
<GaryDolliver[ at ]discussions.microsoft.com> wrote:
The other option is a few lines of VBA code to loop over the recordset
and compare the value read with the expected value.
-Tom.
Microsoft Access MVP
[Quoted Text] >Hi all,
>Is it possible to find missing numbers from a sequence? For example, if my
>table data (in a column) is 1,2,3,4,6,7,8,9,11,12... the desired result
>would be 5,10
>Is this something that can be done? A while back, someone mentioned setting
>up another table that has the complete number sequence and then do a
>comparison, but is there something else that can be done?
>We have some buggy web order importing going on, and I want to be able to
>catch these misses so they are not forgotten.
>Help is appreciated, thanks
>-gary
|
|
Yes but...
For I = StartSeqNum to EndSeqNum
If I <> someTable.OrderNumber then
add I to an exception table
I = I + 1
End If
Next I.
Is a rough idea, but...
You probably can't determine if the first or last number is missing until
you get the next batch.
I've not tried incrementing a counter in a loop since mbasic on a TRS-80 and
it was not always safe then.
The table idea is far safer and a simple query would show what is missing
and could help with duplicates, open orders, etc.
In the mean time please send me one of everything you sell on a missing
order number :)
Gary Dolliver wrote:
[Quoted Text] > Hi all,
> Is it possible to find missing numbers from a sequence? For example,
> if my table data (in a column) is 1,2,3,4,6,7,8,9,11,12... the
> desired result would be 5,10
> Is this something that can be done? A while back, someone mentioned
> setting up another table that has the complete number sequence and
> then do a comparison, but is there something else that can be done?
> We have some buggy web order importing going on, and I want to be
> able to catch these misses so they are not forgotten.
> Help is appreciated, thanks
> -gary
|
|
On Wed, 26 Nov 2008 20:15:01 -0800, Gary Dolliver
<GaryDolliver[ at ]discussions.microsoft.com> wrote:
[Quoted Text] >Hi all,
>Is it possible to find missing numbers from a sequence? For example, if my
>table data (in a column) is 1,2,3,4,6,7,8,9,11,12... the desired result
>would be 5,10
>Is this something that can be done? A while back, someone mentioned setting
>up another table that has the complete number sequence and then do a
>comparison, but is there something else that can be done?
>We have some buggy web order importing going on, and I want to be able to
>catch these misses so they are not forgotten.
>Help is appreciated, thanks
>-gary
One way is to use a Self Join query to find the first value in each gap:
SELECT A.fieldname + 1 AS StartOfGap
FROM yourtable AS A LEFT JOIN yourtable AS B
ON A.fieldname + 1 = B.Fieldname
WHERE B.fieldname IS NULL;
A more complex query can find the entire gap, but it's late and I'm sleepy...
--
John W. Vinson [MVP]
|
|
Thank you John, as always, you are a life saver
-gary
"John W. Vinson" wrote:
[Quoted Text] > On Wed, 26 Nov 2008 20:15:01 -0800, Gary Dolliver
> <GaryDolliver[ at ]discussions.microsoft.com> wrote:
>
> >Hi all,
> >Is it possible to find missing numbers from a sequence? For example, if my
> >table data (in a column) is 1,2,3,4,6,7,8,9,11,12... the desired result
> >would be 5,10
> >Is this something that can be done? A while back, someone mentioned setting
> >up another table that has the complete number sequence and then do a
> >comparison, but is there something else that can be done?
> >We have some buggy web order importing going on, and I want to be able to
> >catch these misses so they are not forgotten.
> >Help is appreciated, thanks
> >-gary
>
> One way is to use a Self Join query to find the first value in each gap:
>
> SELECT A.fieldname + 1 AS StartOfGap
> FROM yourtable AS A LEFT JOIN yourtable AS B
> ON A.fieldname + 1 = B.Fieldname
> WHERE B.fieldname IS NULL;
>
> A more complex query can find the entire gap, but it's late and I'm sleepy...
> --
>
> John W. Vinson [MVP]
>
|
|
thank you for the post and ideas! Thankfully, the web site is still testing
and I just wanted to have a type of back up just to double check and be sure
all was coming in correctly
"Mike Painter" wrote:
[Quoted Text] > Yes but...
> For I = StartSeqNum to EndSeqNum
> If I <> someTable.OrderNumber then
> add I to an exception table
> I = I + 1
> End If
> Next I.
> Is a rough idea, but...
> You probably can't determine if the first or last number is missing until
> you get the next batch.
> I've not tried incrementing a counter in a loop since mbasic on a TRS-80 and
> it was not always safe then.
>
> The table idea is far safer and a simple query would show what is missing
> and could help with duplicates, open orders, etc.
>
> In the mean time please send me one of everything you sell on a missing
> order number :)
>
>
>
> Gary Dolliver wrote:
> > Hi all,
> > Is it possible to find missing numbers from a sequence? For example,
> > if my table data (in a column) is 1,2,3,4,6,7,8,9,11,12... the
> > desired result would be 5,10
> > Is this something that can be done? A while back, someone mentioned
> > setting up another table that has the complete number sequence and
> > then do a comparison, but is there something else that can be done?
> > We have some buggy web order importing going on, and I want to be
> > able to catch these misses so they are not forgotten.
> > Help is appreciated, thanks
> > -gary
>
>
>
|
|
|