Gaps in a sequence number

[Robert McGregor]

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.

[PC Datasheet]

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******@pcdata sheet.com
www.pcdatasheet.com

"Robert McGregor" <rm*******@fire net.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.

[pietlinden]

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 PopulateSequenc es(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.OpenRecordse t("tblSequences ", dbOpenTable)
For lngCounter = 1 To lngRecsToAdd
rs.AddNew
rs.Fields("sequ enceNo") = 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.se quenceNo
FROM tblSequences LEFT JOIN tblApartments ON tblSequences.se quenceNo =
tblApartments.A ptID
WHERE (((tblApartment s.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.

[PC Datasheet]

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******@pcdata sheet.com
www.pcdatasheet.com
<pi********@hot mail.com> wrote in message
news:11******** *************@z 14g2000cwz.goog legroups.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 PopulateSequenc es(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.OpenRecordse t("tblSequences ", dbOpenTable)
For lngCounter = 1 To lngRecsToAdd
rs.AddNew
rs.Fields("sequ enceNo") = 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.se quenceNo
FROM tblSequences LEFT JOIN tblApartments ON tblSequences.se quenceNo =
tblApartments.A ptID
WHERE (((tblApartment s.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.