By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,300 Members | 1,795 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,300 IT Pros & Developers. It's quick & easy.

Gaps in a sequence number

P: n/a
Hi there,

I was wondering if anyone could help with this problem.

I have a table with about 250,000 rows that relate to files that have
been processed elsewhere in our business. Each file has a sequence
number as part of the file name that goes from 0000 to 9999 then back
to 0000.

I want to output a list to tell me the missing entries in that list
(or the entries directly before and after the gaps) so i can go and
investigate what has happened to those files.

Sorry if this seems like a pointless problem but I really need an
answer ASAP.

Many thanks,

Rob.
Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Please give us some examples of file names and explain what you mean by
"...then back to 0000."

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com

"Robert McGregor" <rm*******@firenet.uk.com> wrote in message
news:8d**********************@posting.google.com.. .
Hi there,

I was wondering if anyone could help with this problem.

I have a table with about 250,000 rows that relate to files that have
been processed elsewhere in our business. Each file has a sequence
number as part of the file name that goes from 0000 to 9999 then back
to 0000.

I want to output a list to tell me the missing entries in that list
(or the entries directly before and after the gaps) so i can go and
investigate what has happened to those files.

Sorry if this seems like a pointless problem but I really need an
answer ASAP.

Many thanks,

Rob.

Nov 13 '05 #2

P: n/a
I assume it means count off 0 to 9999 and then restart at zero... kind
of a bad idea if you're going to have more than 10K records, though...
Umm... find the gaps... how about create a numberlist table and
populate it with some code...

Something like this:

Create Sequences code:
Option Compare Database
Option Explicit

Public Sub PopulateSequences(ByVal lngRecsToAdd As Long)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim lngCounter As Long

Set db = CurrentDb
' didn't like dbappendonly... huh? (wouldn't compile)
Set rs = db.OpenRecordset("tblSequences", dbOpenTable)
For lngCounter = 1 To lngRecsToAdd
rs.AddNew
rs.Fields("sequenceNo") = lngCounter
rs.Update
Next lngCounter

rs.Close
Set rs = Nothing
Set db = Nothing

End Sub

Create Find Unmatched Query: ==>returns all the sequences that aren't
"used up" SELECT tblSequences.sequenceNo
FROM tblSequences LEFT JOIN tblApartments ON tblSequences.sequenceNo =
tblApartments.AptID
WHERE (((tblApartments.AptID) Is Null));

Checking for gaps where you return lngBeginGap and lngEndGap is a bit
trickier. You have to open the recordset (forwardonly is fine) and
then have a counter increment as you loop through the records. If the
counter and the value of the record's field are not the same, then
there's a gap, so write the last "sequence number" somewhere. Then
increment the counter until it is equal to the next number in the
table/recordset. write that down and continue. Then you could just
write the gaps to another table or something.

Nov 13 '05 #3

P: n/a
My thought was to create a table of numbers from 0 to 9999 and then use an
unmatched query to look for missing numbers. Need to see the file names to
say how to apply that.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com
<pi********@hotmail.com> wrote in message
news:11*********************@z14g2000cwz.googlegro ups.com...
I assume it means count off 0 to 9999 and then restart at zero... kind
of a bad idea if you're going to have more than 10K records, though...
Umm... find the gaps... how about create a numberlist table and
populate it with some code...

Something like this:

Create Sequences code:
Option Compare Database
Option Explicit

Public Sub PopulateSequences(ByVal lngRecsToAdd As Long)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim lngCounter As Long

Set db = CurrentDb
' didn't like dbappendonly... huh? (wouldn't compile)
Set rs = db.OpenRecordset("tblSequences", dbOpenTable)
For lngCounter = 1 To lngRecsToAdd
rs.AddNew
rs.Fields("sequenceNo") = lngCounter
rs.Update
Next lngCounter

rs.Close
Set rs = Nothing
Set db = Nothing

End Sub

Create Find Unmatched Query: ==>returns all the sequences that aren't
"used up" SELECT tblSequences.sequenceNo
FROM tblSequences LEFT JOIN tblApartments ON tblSequences.sequenceNo =
tblApartments.AptID
WHERE (((tblApartments.AptID) Is Null));

Checking for gaps where you return lngBeginGap and lngEndGap is a bit
trickier. You have to open the recordset (forwardonly is fine) and
then have a counter increment as you loop through the records. If the
counter and the value of the record's field are not the same, then
there's a gap, so write the last "sequence number" somewhere. Then
increment the counter until it is equal to the next number in the
table/recordset. write that down and continue. Then you could just
write the gaps to another table or something.

Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.