Finding missing numbers in a Sequential field

[jjpuebla]

Hi everyone, I have a problem. I hope you can help me, please!

I have a table with the sales of an specific day. This table has among other information the invoice numbers in a sequential order. I want to create a query to show if there is an invoice that is not in the sequential. For example, if yesterday's invoices start with invoice number 107015 and end with invoice 107610, I would like to know if there is one or more missing invoice numbers within that range.

Thanks and have a great day...

[Gicu]

The easiest would be to create a new table and populate it with the entire sequence (you can do that in code or even Excel then copy and paste in Access), then use the Find Unmatched query wizard (basically will create a Left Outer join between your tblAllInvoiceSequence.InvoiceNumber to your tblInvoice.InvoiceNumber and put Is Null in the criteria field.
https://www.fmsinc.com/MicrosoftAcce...oin/index.html

Cheers,

[CJ_London]

perhaps code like this?

SELECT tblinv.invdate, M.MissingFrom, M.MissingTo
FROM tblinv INNER JOIN (SELECT S.invdate, S.[invnum], Max(tblinv.[invnum]+1) AS MissingFrom, S.MissingTo FROM tblinv INNER JOIN (SELECT S.invdate, S.[invnum], S.[invnum]-1 AS MissingTo FROM tblinv AS S LEFT JOIN tblinv AS E ON (S.invdate = E.invdate) AND (S.[invnum]-1=E.[invnum]) WHERE (((E.[invnum]) Is Null)))  AS S ON tblinv.invdate=S.invdate WHERE (((tblinv.[invnum])<[MissingTo])) GROUP BY S.invdate, S.[invnum], S.MissingTo)  AS M ON (tblinv.[invnum] = M.[invnum]) AND (tblinv.invdate = M.invdate)
ORDER BY tblinv.invdate, M.MissingFrom;

from this data

invDate	invNum
26/08/2020	1
26/08/2020	2
26/08/2020	4
26/08/2020	5
26/08/2020	8
26/08/2020	9
26/08/2020	10

it produces this result

invdate	MissingFrom	MissingTo
26/08/2020	3	3
26/08/2020	6	7

[jjpuebla]

Gicu, thanks for your reply and I already try your solution. However, the database it is going to be used for users that I would like them to use the least effort and offer a direct solution (press a button and magicly, the missings numbers appear).

[jjpuebla]

Ajax thanks for your reply, however I am kind of lost. The first table you wrote I think is "tblinv". Where are the other tables "S" and "M". Sorry if it is a dumb question, kind of a beginner here...

[CJ_London]

S is an alias of tblInv > 'tblinv AS S'

as is E > 'tblinv AS E'

S is also an alias for a query (sorry, could have given it a different letter)

(SELECT S.invdate, S.[invnum], S.[invnum]-1 AS MissingTo 
FROM tblinv AS S LEFT JOIN tblinv AS E ON (S.invdate = E.invdate) AND (S.[invnum]-1=E.[invnum]) 
WHERE (((E.[invnum]) Is Null)))  AS S

And M is an alias for another query

(SELECT S.invdate, S.[invnum], Max(tblinv.[invnum]+1) AS MissingFrom, S.MissingTo 
FROM tblinv INNER JOIN (SELECT S.invdate, S.[invnum], S.[invnum]-1 AS MissingTo FROM tblinv AS S LEFT JOIN tblinv AS E ON (S.invdate = E.invdate) AND (S.[invnum]-1=E.[invnum]) WHERE (((E.[invnum]) Is Null)))  AS S ON tblinv.invdate=S.invdate 
WHERE (((tblinv.[invnum])<[MissingTo])) 
GROUP BY S.invdate, S.[invnum], S.MissingTo)  AS M

you can write these other queries as separate queries if you wish and then combine into 1 query

And I use alias's to reduce the amount of typing and to indicated what the alias is about

S=Start
E=End
M=Missing

(it works for me)

I have the code as a broad template query for this sort of scenario which can be used in many tables just by making a few simple changes. So if this was for say identifying missing employee hours I might change tblInv to tblEmpTimes, invnum to startTime and invDate to ClockDate- the alias's stay the same

[jjpuebla]

Thanks for your response, now everything it is clear!