Solved Missing Numbers in Sequence

[raziel3]

Hello all,
I am trying to make a query to get numbers missing in a sequence. So far I've used this

SELECT CHQREGISTER.CHQNUM, (SELECT TOP 1 T1.CHQNUM                
   FROM CHQREGISTER AS T1                    
   WHERE T1.CHQNUM = CHQREGISTER.CHQNUM+1
   ORDER BY T1.CHQNUM ASC) AS SEQ, IIf(IsNull([SEQ]),[CHQNUM]+1,"") AS MISSING
FROM CHQREGISTER
WHERE ((((SELECT TOP 1 T1.CHQNUM                
   FROM CHQREGISTER AS T1                    
   WHERE T1.CHQNUM = CHQREGISTER.CHQNUM+1
   ORDER BY T1.CHQNUM ASC)) Is Null))
ORDER BY CHQREGISTER.CHQNUM;

The results:

Unfiltering the "MISSING" field gives me this:

As you can see numbers 503, 504, 505 are undetected by the query. My intention is to create a temporary table from CHQREGISTER listing all the numbers in sequence to the MAX CHQNUM then join CHQREGISTER to it.

How would I go about creating that temporary table?

 

[Pat Hartman]

There may be a way to do this with a query but the method to find all the missing numbers isn't immediately coming to mind. I would do it with a recordset. Create a query that sorts by the sequence number. Then create a loop that writes out all the missing numbers to the temp table. Keep the temp output table recordset open and use .AddNew

Open Recordset
    SavedseqNum = rs.SeqNum
    Do Until rs.EOF = False
        If rs.seqNum > SavedseqNum
            SavedSeqNum = SavedSeqNum +1
            rsTemp.AddNew to temp table
            If rs.SeqNum > SavedSeqNum
            Else
                MoveNext
            End If
        Else
                SavedseqNum = rs.SeqNum
        End If
    Loop

 

[plog]

In Access you can't have a query create more records than in its underlying datasource. If you have 3 records in CHQREGISTER, your query is only going to produce 3 records.

So, you need a datasource with as many records as you might have checks. Once you have that you can that as your main table and LEFT JOIN CHQREGISTER to it and then find out which ones exist and which ones don't and generate the query you want. There's a few ways to make that datasource of all numbers:

1. Open excel, put =1 in A1 put =A1 + 1 in B1 and then copy B12 formula down the column so that it gives you a sequential list of numbers. Import that into Access and call it TargetNumbers and name the field TargetNumber.

2. Make a table called BaseNumbers, with 1 field called Digit and add 10 records using the values 0-9. Then make a query using 3 instances of the table but don't link them. Have just one calculated field and use this:

TargetNumber: BaseNumbers.Digit *100 + BaseNumbers_1.Digit*10 + BaseNumbers_2.Digit

Name that query TargetNumbers, it will produce all the numbers from 0-999. If you need more, add another instance of Numbers and expand the calculation.

Now to find missing numbers in CHQREGISTER you use this query:

SELECT TargetNumber
FROM TargetNumbers
LEFT JOIN CHQREGISTER ON CHQNUM=TargetNumber
WHERE CHQNUM IS NULL

 

[The_Doc_Man]

The last time I did this, I had to use a recordset and check-number counter in VBA. When needed, I had to create blank records, with a marker in the recordset to show that it was a synthesized rather than natural check record. A query wouldn't handle long gaps very well - like the time I skipped a checkbook and once I started, didn't get the right checkbook until I emptied the one that was started out of sequence. Eventually it all balanced but oh man was it a mess!

 

[arnelgp]

you need to somehow Save the Check Start/Ending Sequence of each booklet to a table (ChequesT).
i made another table (OneToHundredT), just to add this to the ChequesT start Sequence to
generate the sequence for that booklet (see 1_CheckSequenceQ query, for the generated sequence).
TransactionT holds the transaction where a cheque is used.
2_UsedChequeSeries query will hold which Cheque Series booklet each cheques belongs.
query 3_ChequeStatus is the "missing" cheque query.

cheque series (20020 to 20070) is not included since it's series is not yet included in the transaction table.

 

[raziel3]

@arnelgp that was along the lines of what I'm thinking to do. I know it can't be done with a query so I was thinking something along the lines of this in VBA. Instead of creating the tables.....

Step 1 - DMax the cheque register to get the largest Cheque Number
Step 2 - Create a Recordset from 1 to Max(Cheque Number) Step 1 (Need help, I'm not too good when it comes to opening and closing recordsets in VBA)
Step 3 - Left Join the newly formed Recordset to the CHQREGISTER Table and filter the unmatched records
Step 4 - Used the results as the Recordsource for a report.

On a button, Open the Report
On the Load Event for the Report, Execute the code.

What do you all think?

 

[ebs17]

make a query to get numbers missing in a sequence

The simple and classical method to find missing values is to match a complete set with the existing set. @plog shows this convincingly.
Creating a number help table like TargetNumbers is a one-time effort, but with good planning, such a table can be profitably used in many other cases. In my case a table named T999 with the contained numbers from 0 to 999 (only field is primary key) covers the usual cases sufficiently.

But maybe it is intended to display missing numbers as contiguous areas and thus increase the overview by the compressed display.
This could be solved in a query like this:

SELECT
   X1.StartGap,
   MIN(X2.EndGap) AS EndGap
FROM
   (
      SELECT
         T1.CHQNUM + 1 AS StartGap
      FROM
         CHQREGISTER AS T1
            LEFT JOIN CHQREGISTER AS T2
            ON T1.CHQNUM + 1 = T2.CHQNUM
      WHERE
         T2.CHQNUM is null
            AND
         T1.CHQNUM <
            (
               SELECT
                  MAX(CHQNUM)
               FROM
                  CHQREGISTER
            )
         ) AS X1
      INNER JOIN
         (
               SELECT
                  T1.CHQNUM - 1 AS EndGap
               FROM
                  CHQREGISTER AS T1
                     LEFT JOIN CHQREGISTER AS T2
                     ON T1.CHQNUM - 1 = T2.CHQNUM
               WHERE
                  T2.CHQNUM is null
                     AND
                  T1.CHQNUM >
                     (
                        SELECT
                           MIN(CHQNUM)
                        FROM
                           CHQREGISTER
                     )
                  ) AS X2
            ON X1.StartGap <= X2.EndGap
GROUP BY
   X1.StartGap

 

[arnelgp]

On a button, Open the Report

you can use Temp table (TempT on the demo).
see the Open Event of the Report how the
CheckNum is populated in TempT.

The recordsource of the report is the Query Query1.

 

[raziel3]

This is perfect @arnelgp

Thank You.

 

[raziel3]

Just wanted to share the code I added to my database (all credit to @arnelgp) for finding missing numbers in a sequence.

    Dim rs As DAO.Recordset
    Dim db As DAO.Database   
    Dim MAXCHQ As Long
    Dim i As Long
       
    MAXCHQ = DMax("CHQNUM", "CHQREGISTER")     ' get the max cheque number from table
   
    Set db = CurrentDb
    db.Execute "delete * from MISSINGCHQS"
    Set rs = db.OpenRecordset("MISSINGCHQS")
       
    With rs
        For i = 1 To MAXCHQ
            If DCount("CHQNUM", "CHQREGISTER", "CHQNUM=" & i) = 0 Then     'add only the missing Cheques
                SysCmd acSysCmdInitMeter, "Finding Missing Cheques", MAXCHQ 
                .AddNew
                !CHQNUM = CStr(i)
                .Update
                SysCmd acSysCmdUpdateMeter, i 'progress bar
            End If
        Next
        .Close
    End With
    SysCmd acSysCmdRemoveMeter
    MsgBox DCount("CHQNUM", "MISSINGCHQS") & " Missing Cheques were found.", vbInformation, "Missing Cheque Finder"

 

[Pat Hartman]

This method works but it is extremely inefficient. Each domain function runs a query so you are running x additional queries with x being the number of rows in the recordset. I wouldn't use it for any recordset of more than a thousand records.

Domain functions are an excellent tool when used in one off situations such as your message box at the end. They should always be avoided in code loops and queries. If you stick with this method (and I'm not suggesting that you shouldn't given the potential size of your recordset), I would switch to the replacement domain functions by "Roger" i think. Someone will post a link. I don't have one handy.

I gave you pseudocode for a single pass through a recordset to find the gaps which is a more efficient method for your specific request. The other suggestions that create "numbers" tables will also work and could be the most efficient method if your recordset is large because the database engine optimizes joins very well but the "numbers" (it may be dates or numbers) table must contain a row for each number in the universe of numbers. That means that as your source recordset grows, periodically you need to add a new bunch of numbers/dates to the "numbers" table. I use this method for a client who needs a count of boxes in a warehouse for each day of a billing period. Since the billing period was a calendar month, I didn't need to generate a table with specific dates. I could use a table with 31 rows and a criteria that selected 28-31 based on the year and month.

 

[ebs17]

I would switch to the replacement domain functions by "Roger"

However, these have the same problem if they are systematically executed inappropriately per record.

 

[Pat Hartman]

Yes, they have the same problem but at least they execute more quickly so if the OP is going to stick with the inferior solution, at least it will be as fast as it can be.

 

[gemma-the-husky]

The problem really is that when you get a missing number, the code you used reports the next number, but then doesn't report any further missing numbers if there is more than a single missing number.

Whatever you do, you will get a problem if you use non-contiguous cheque sequences. (eg, one sequence for cash book cheques, and another for ledger cheques), as you will most likely have at least 2 active sequences.

 

[CJ_London]

I use a non standard join to show the missing range of records

SELECT Max(B.EntryPK) +1 AS MissingFrom, A.EntryPK-1 as MissingTo
FROM myTable AS A INNER JOIN myTable AS B ON A.EntryPK > B.EntryPK
GROUP BY A.EntryPK
HAVING Max(B.EntryPK)<>[A].[entryPK]-1;

Has to be written in sql or to use the QBE, create a standard join, then go to sql view and change the = to >

helps if the field is indexed and if there are separate sequences, the two tables can be joined with a standard join

 

[raziel3]

Whatever you do, you will get a problem if you use non-contiguous cheque sequences. (eg, one sequence for cash book cheques, and another for ledger cheques), as you will most likely have at least 2 active sequences.

Poor Accounting practice. (Using multiple cheque books at the same time that is.)

So @Pat Hartman, once you open the recordset within a loop it remains open until the loop is finished?

As I said before, I don't fully understand recordsets as yet.

 

[Pat Hartman]

So @Pat Hartman, once you open the recordset within a loop it remains open until the loop is finished?

Yes. You open one recordset sorted by ID and read through it. If you find a gap, you use .addnew to add the record and then read next. The sample code stays on the record it read until you insert all the necessary gap records.

If you are using an autonumber and that is what is causing the gaps, you need to switch to code which generates the next sequence number and doesn't create gaps when you cancel an insert.

 

[gemma-the-husky]

Poor Accounting practice. (Using multiple cheque books at the same time that is.)

So @Pat Hartman, once you open the recordset within a loop it remains open until the loop is finished?

As I said before, I don't fully understand recordsets as yet.

Hardly. Many businesses will have separate sequences for manually written cheques, and automated ledger cheques. If you have pre-printed cheques in a spooled printer, you are quite likely to "burn" a few cheques between cheque runs.

My wife and I have separate cheque books on our joint account.

 

[Pat Hartman]

When you are dealing with printed checks, there is ALWAYS a record for the gap, usually with a reason for there being a gap. Check blanks are like money.

 

[gemma-the-husky]

I was just giving reasons why a record sequence may not be intact.

Creating a record and recording a reason for a spoilt/unused cheque is a different matter.

In order to ascertain whether you have gaps, you really do need a start and end number in the sequence you are testing, and maintaining multiple sequences is not automatically an error or a failing.