p2p.wrox.com Forums

Need to download code?

View our list of code downloads.


  Return to Index  

access thread: Return missing sequence Numbers


Message #1 by Omar Chaudry <OChaudry@b...> on Thu, 27 Jun 2002 12:09:10 +0100
This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.

------_=_NextPart_001_01C21DCB.1014A430
Content-Type: text/plain

Dear All

I have a requirement to return all missing sequence numbers from a list of
numbers for instance if my list contains the following numbers:-

1,2,3,5,6,7,8,9,10,12,14,15 

I need to be able to return in a query 

4, 11, 13.

Any ideas on now to accomplish this.

 

Many thanks

Omar

 



  DISCLAIMER: The information in this message is confidential and may be
legally privileged. It is intended solely for the addressee.  Access to this
message by anyone else is unauthorised.  If you are not the intended
recipient, any disclosure, copying, or distribution of the message, or any
action or omission taken by you in reliance on it, is prohibited and may be
unlawful.  Please immediately contact the sender if you have received this
message in error. Thank you.



Message #2 by "Amy Wyatt" <amyw@c...> on Thu, 27 Jun 2002 12:53:28
I've done it two ways, the easiest is to create a table with the values 1 
trhough whatever an then do a unmatched query to return what's missing in 
your list as compared to the other list.

The other way is to check via a loop against what is already there, that 
way you only have to check to the max value of the current list. For 
Example in the list gave you could do a For loop For x=1 to 15 and check 
for each value of x in the current record set.

Hope this helps,

Amy

> This message is in MIME format. Since your mail reader does not 
understand
this format, some or all of this message may not be legible.

------_=_NextPart_001_01C21DCB.1014A430
Content-Type: text/plain

Dear All

I have a requirement to return all missing sequence numbers from a list of
numbers for instance if my list contains the following numbers:-

1,2,3,5,6,7,8,9,10,12,14,15 

I need to be able to return in a query 

4, 11, 13.

Any ideas on now to accomplish this.

 

Many thanks

Omar

 



  DISCLAIMER: The information in this message is confidential and may be
legally privileged. It is intended solely for the addressee.  Access to 
this
message by anyone else is unauthorised.  If you are not the intended
recipient, any disclosure, copying, or distribution of the message, or any
action or omission taken by you in reliance on it, is prohibited and may be
unlawful.  Please immediately contact the sender if you have received this
message in error. Thank you.



Message #3 by Omar Chaudry <OChaudry@b...> on Thu, 27 Jun 2002 16:13:49 +0100
Thanks Amy et al.

Now for the slight twist in the tale! Going back to my original example
consider a date field as well so
NEW TABLE
---------
Ref	Date
---	----
1	1/1/01
2	2/1/01
3	3/1/01
6	4/1/01	'note 2 missing numbers

...

OK I write a query to return missing sequence i.e. 4 & 5 in above using
either of the two methods you mentioned. I also need to capture the Date
value for the sequence number immediately before the missing sequence or the
first number backwards which has a date value in the example above I need to
return 4 & 5 and the date for 3 against EACH. I have tried a number of
things including writing a UDF but to no avail. Note that I have around
20000 rows to traverse through and growing!!

Many thanks again

Omar
	

-----Original Message-----
From: Amy Wyatt [mailto:amyw@c...] 
Sent: 27 June 2002 13:53
To: Access
Subject: [access] Re: Return missing sequence Numbers

I've done it two ways, the easiest is to create a table with the values 1 
trhough whatever an then do a unmatched query to return what's missing in 
your list as compared to the other list.

The other way is to check via a loop against what is already there, that 
way you only have to check to the max value of the current list. For 
Example in the list gave you could do a For loop For x=1 to 15 and check 
for each value of x in the current record set.

Hope this helps,

Amy

> This message is in MIME format. Since your mail reader does not 
understand
this format, some or all of this message may not be legible.

------_=_NextPart_001_01C21DCB.1014A430
Content-Type: text/plain

Dear All

I have a requirement to return all missing sequence numbers from a list of
numbers for instance if my list contains the following numbers:-

1,2,3,5,6,7,8,9,10,12,14,15 

I need to be able to return in a query 

4, 11, 13.

Any ideas on now to accomplish this.

 

Many thanks

Omar

 



  DISCLAIMER: The information in this message is confidential and may be
legally privileged. It is intended solely for the addressee.  Access to 
this
message by anyone else is unauthorised.  If you are not the intended
recipient, any disclosure, copying, or distribution of the message, or any
action or omission taken by you in reliance on it, is prohibited and may be
unlawful.  Please immediately contact the sender if you have received this
message in error. Thank you.





  DISCLAIMER: The information in this message is confidential and may be
legally privileged. It is intended solely for the addressee.  Access to this
message by anyone else is unauthorised.  If you are not the intended
recipient, any disclosure, copying, or distribution of the message, or any
action or omission taken by you in reliance on it, is prohibited and may be
unlawful.  Please immediately contact the sender if you have received this
message in error. Thank you.


Message #4 by Richard Gibson <rgibson@W...> on Fri, 28 Jun 2002 08:56:52 +0100
Omar,  I would create a recordset based on the relevant table (make sure the
order is number ascending)

start with the first record and capture the number and date

 
if the current number is greater than the captured value + 1 you know there
is a missing record.
Add the record (using the value of captured number +1) and the date you've
already captured and update.

If it equals captured value + 1 do nothing

move to the next record

Loop this and it should do the job.








Regards

Richard Gibson
IT Manager


The information in this message is confidential and intended for the
addressee only.  If you have received this message in error please delete
and notify the sender, any other action may be unlawful.

The views expressed in this message are personal and not necessarily those
of Walkerpack Ltd unless explicitly stated.


-----Original Message-----
From: Omar Chaudry [mailto:OChaudry@b...]
Sent: 27 June 2002 16:14
To: Access
Subject: [access] Re: Return missing sequence Numbers


Thanks Amy et al.

Now for the slight twist in the tale! Going back to my original example
consider a date field as well so
NEW TABLE
---------
Ref	Date
---	----
1	1/1/01
2	2/1/01
3	3/1/01
6	4/1/01	'note 2 missing numbers

...

OK I write a query to return missing sequence i.e. 4 & 5 in above using
either of the two methods you mentioned. I also need to capture the Date
value for the sequence number immediately before the missing sequence or the
first number backwards which has a date value in the example above I need to
return 4 & 5 and the date for 3 against EACH. I have tried a number of
things including writing a UDF but to no avail. Note that I have around
20000 rows to traverse through and growing!!

Many thanks again

Omar
	

-----Original Message-----
From: Amy Wyatt [mailto:amyw@c...] 
Sent: 27 June 2002 13:53
To: Access
Subject: [access] Re: Return missing sequence Numbers

I've done it two ways, the easiest is to create a table with the values 1 
trhough whatever an then do a unmatched query to return what's missing in 
your list as compared to the other list.

The other way is to check via a loop against what is already there, that 
way you only have to check to the max value of the current list. For 
Example in the list gave you could do a For loop For x=1 to 15 and check 
for each value of x in the current record set.

Hope this helps,

Amy

> This message is in MIME format. Since your mail reader does not 
understand
this format, some or all of this message may not be legible.

------_=_NextPart_001_01C21DCB.1014A430
Content-Type: text/plain

Dear All

I have a requirement to return all missing sequence numbers from a list of
numbers for instance if my list contains the following numbers:-

1,2,3,5,6,7,8,9,10,12,14,15 

I need to be able to return in a query 

4, 11, 13.

Any ideas on now to accomplish this.

 

Many thanks

Omar

 



  DISCLAIMER: The information in this message is confidential and may be
legally privileged. It is intended solely for the addressee.  Access to 
this
message by anyone else is unauthorised.  If you are not the intended
recipient, any disclosure, copying, or distribution of the message, or any
action or omission taken by you in reliance on it, is prohibited and may be
unlawful.  Please immediately contact the sender if you have received this
message in error. Thank you.





  DISCLAIMER: The information in this message is confidential and may be
legally privileged. It is intended solely for the addressee.  Access to this
message by anyone else is unauthorised.  If you are not the intended
recipient, any disclosure, copying, or distribution of the message, or any
action or omission taken by you in reliance on it, is prohibited and may be
unlawful.  Please immediately contact the sender if you have received this
message in error. Thank you.


Message #5 by "Ben Clark" <clarkbm@p...> on Fri, 28 Jun 2002 13:28:52
Omar,

You can expand on Amy's suggestion of a For-Next loop to get the results 
you want.  Forgive my early morning use of psuedo-code, but something like 
this should give you about what you want:

Dim x as integer                'loop counter
dim last_good_date as date      'track the last known good date
dim first_missing_ref as int    'track the first missing ref
dim missing_recs as int         'track the number of missing recs

missing_recs = 0
For x = 1 To <# records>
    If x = Ref then
        if missing_recs > 0 then save data
        last_good_date = Date
        missing_recs = 0
    Else
        missing_recs = missing_recs + 1
        if missing_recs = 1 then first_missing_ref = x
    End If
Next 'x

The If block will save data if it has just come out of a gap, then reset 
the last_good_date to the current value, and finally reset the 
missing_recs counter to 0.  The Else block will iterate the missing_recs 
counter while it scans a gap, and set the first_missing_ref to x only if 
it's on the first missing Ref of a gap.

A do/while not rec.EOF loop will work just as well.  The data this saves 
should be the first missing Ref in a gap, the number of missing Refs in 
the gap, and the last good date preceding the gap.

The drawback, of course, is the time it takes to loop through 20000+ 
records...

Hope this helps.  Heck, I just hope it works.
Ben


Now for the slight twist in the tale! Going back to my original example
consider a date field as well so
NEW TABLE
---------
Ref	Date
---	----
1	1/1/01
2	2/1/01
3	3/1/01
6	4/1/01	'note 2 missing numbers

...

OK I write a query to return missing sequence i.e. 4 & 5 in above using
either of the two methods you mentioned. I also need to capture the Date
value for the sequence number immediately before the missing sequence or 
the
first number backwards which has a date value in the example above I need 
to
return 4 & 5 and the date for 3 against EACH. I have tried a number of
things including writing a UDF but to no avail. Note that I have around
20000 rows to traverse through and growing!!

Many thanks again

Omar

  Return to Index