Checking for sequence break 1

[Robotron]

How would I show records where the value in the RecordId(autonumber) field is not equal to the previous record's RecordID + 1? I am wanting to show where there are gaps in the sequence.

 

[Golom]

Try
[blue][tt]
Select DISTINCT A.RecordID
From tbl A INNER JOIN tbl B
ON B.RecordID < A.RecordID - 1
[/tt][/blue]

 

[Robotron]

Golom,

Thanks for the tip. I tried this. It sorts the ID field ascending. I was not expecting it to do this. Let me elaborate a bit more. I have the following records in the table:

RecordID
--------
261573
6
7
2780

I would like the query to return the records that have 6 and 2780 as RecordID because they break the sequence.

 

[Golom]

OK. I had assumed that the "sequence" to which you referred was the RecordID sequence. You clearly have some other mechanism for determining record sequence since your example shows RecordIDs that are in no particular order (i.e. Ascending or Descending). Assuming that the sequence is determined by some field called "fldx" (for example) then
[blue][tt]
Select DISTINCT A.RecordID
From tbl A, tbl B
Where B.fldx =
(Select MAX(fldx) From tbl X
Where x.fldx < a.fldx)

AND A.RecordID <> B.RecordID + 1
[/tt][/blue]

 

[Robotron]

Golom,

There is no other mechanism or field that determines record sequence (if I am understanding you correctly). I am attempting to keep the records in the current order and display those records where the value in RecordID <> (the value in the previous record) + 1. Disregard the information in my first post about RecordID being in autonumber format.

 

[Golom]

OK. Here's the problem. In access, or any RDBMS, there is no inherent ordering of records. To put it another way, the concept of "next" record or "previous" record is undefined except within the confines of relative values of some field or fields.

Of course a query will return records with some apparent ordering but, without an ORDER BY clause, SQL does not guarantee that records will be returned in any particular order ... hence with any particular "previous" or "next" record.

Without some definition of what is meant by "previous record" in terms of a comparison of field values, there can be no answer to your question.

 

[Robotron]

Thanks. My first inclination was to add a field to the table with an autonumber so I could facilitate what I am attempting to do. In speaking with the designer of the database about this, I learned that he would prefer not to add a field to the table as I suggested. I appreciate your information. I can use it to shed some light on the subject with the designer.