Gaps in records

[TB11]

Hi All. I'm stuck on how to find gaps in records. (I know there have been other threads on this topic, but I am just not getting it, sorry.) I really don't want to have to manually go line by line to find any gaps.

In my table I have a BegNumb and EndNumb. I have done a query to calculate the difference between BegNumb and EndNumb, as well as CalcPriorBegNumb and CalcNextEndNumb. So far so good.

I just can't seem to understand the next steps to show the gaps.

I appreciate any help.

 

[XPS35]

First explain what you mean by a gap and provide example data.

 

[Gasman]

Here is something that @CJ_London offered for someone looking for missing dates.

sqlstr="SELECT A.ReportDate+1 AS MissingDate" & _
" FROM Reports A LEFT JOIN Reports B ON A.ReportDate+1=B.ReportDate" & _
" WHERE B.ReportDate is Null l AND A.ReportDate<>dmax('ReportDate','Reports')"

 

[Mark_]

Please note, if you allow deletion of records you WILL see this.

Normally if you want to make sure you have sequential numbers for... reasons... you'd generate the number AFTER you've added the record. This way you don't have a gap if the user starts a record and doesn't save. Alternate is to create the record when the user goes in to the form in add mode but void the transaction if they cancel out.

Think through what would happen if user A goes in to a form in "Add mode". User B goes in to also add. Who gets what numbers when? Then let user A back out and NOT add the record.

 

[Pat Hartman]

1. If you allow deletes, you ALWAYS have gaps.
2. If you use an autonumber, you ALWAYS have gaps, even if you don't allow deletes because the number is generated before the record is saved and so if the insert doesn't complete, the generated number is lost.
3. If you generate your own sequence number, you have to worry about duplicates and there are situations where you could have gaps.

There is no easy way in a multi-user application, even when you generate sequence numbers with code to guarantee that you don't end up with gaps.

So, we need details.
1. WHY are gaps a problem?
2. How is the number being generated?
3. Are you prepared to write the code to generate your own sequence? This will almost certainly involve creating a process that single threads inserts. This means that UserA MUST generate a number and save the record before UserB can start the process of creating a new record.

 

[TB11]

My apologies for not being clearer. The gaps are not tied to an ID field or when a record is deleted, but rather the BegNumb and EndNumb are from data entry. The gaps may arise because the data entry has a typo, which would result in a gap or some other human error. I already have calculated fields to determine if there is a BegNumb and EndNumb, so I can filter on those issues. @Gasman I'll try the code you pasted (thanks).

 

[June7]

Ideally, data would be validated during data entry, however, sequential nature of this data still complicates any effort in this regard for multi-user db. User A input would have to be saved before User B input could be validated.

We still don't know what constitutes a "gap". Doesn't sound like this data is consecutive, but that the next BegNumb must be greater than or equal to previous EndNumb.

What does this data signify? If this is essentially a meter reading, convention is to NOT save Begin and End values, only the number on meter at time of reading. Calculate the difference of values for consecutive records with a correlated subquery (or the max and min records of a range - usually a time period).

So how do you calculate CalcPriorBegNumb and CalcNextEndNumb?

Sample data as requested could be helpful. Can build a table (or copy/paste) into post or attach file.

 

[TB11]

Gosh I am sorry. I didn't mean to make this difficult for everyone because I was not clear in my question.

The gap would occur between the EndNumb not equaling the BegNumb on the next record, and if these numbers do not equal there is what I am guessing is a gap, and the data entry must be fixed. In the sample csv below, the gaps are at ID 7 and 9, as the BegNumber does not equal EndNumb for ID 6 and 8. The BegNumb and EndNumb are manual data entry. The fields for BegNumb and EndNumb are Long Integer.

Id, BegNumb, EndNumb
5,27,32
6,32,33
7,37,62
8,62,64
9, 68, 71

 

[arnelgp]

i made a Function in Module1 (VBA) and call it in Query1.
open query1 for the records with Gaps.

 

[XPS35]

5,27,32
6,32,33
7,37,62
8,62,64
9, 68, 71

Why do you store both numbers? If the begin number is equal to the previous end number by defenition, there is no need to store both.

In your setup you may also encounter overlapping numbers. Like:
10, 69, 77
Any validation will only tell you there is something wrong. But not what is wrong. The current entry or the previous?

So just store the end number and if you need to show the begin number, find it in the previous record.

 

[MajP]

In Sql you could do something like this

SELECT yourtablename.id,
       yourtablename.begnum,
       yourtablename.endnum,
       (SELECT TOP 1 A.endnum
        FROM   yourtablename AS A
        WHERE  A.id < yourtablename.id
        ORDER  BY id DESC) AS PreviousEnd
FROM   yourtablename
WHERE  (( ( yourtablename.begnum ) <> (SELECT TOP 1 A.endnum
                                       FROM   yourtablename AS A
                                       WHERE  A.id < yourtablename.id
                                       ORDER  BY id DESC) ));

 

[Pat Hartman]

The gap would occur between the EndNumb not equaling the BegNumb on the next record

This is what validation is for as June pointed out. For range data, I also wouldn't save the beginning number as someone else said. Save only the ending value (typically "meter" reading) and validate that it is > than the value on the previous record. You might be able to do more detailed validation if you have history and know an average difference. Then if the new record is some percentages off from the previous record, you can prompt the user to confirm.

It is poor practice to attempt to validate data AFTER you save it. Do it in the BeforeUpdate event of the Form that saves the record so the bad data never gets saved.

 

[June7]

My vote is to delete the BegNumb field and move on.