Missing number query

[Chrisopia]

I have a list of manually in-putted invoice numbers and need to figure out a way to create a list from this to tell me which numbers are missing... there are excess of 1000 entries so far (considering there are over 1300 invoices and counting, some have obviously been forgotten)

Hope you can help/

 

[rainman89]

Ok this is probably ugly, and I'm sure its not the best way to do it, but if i wanted to do it quickly, its how i would do it......

Id create a dummy table with record numbers 1-2000 or whatever. Just numbers. just copy and paste from excel

Then I would do a left join on the records table and your invoice table selecting all records from the records table and just the ones in the invoice table that match....

I'll attach a small sample

AGAIN THIS IS PROBABLY NOT IDEAL, but its how I would do it quickly

 

[Chrisopia]

thats all I need... a quick reference of which numbers are missing.

Thanks for the tool tip ^.^

 

[namliam]

Make a new query:
SELECT Table2.IDInvoice, Table2.InvoiceNumber, [InvoiceNumber]+1 AS NextInv
FROM Table2;

Save as qryNextInvoice

Make another new query:
SELECT qryNextInvoice.NextInv AS MissingInvoices
FROM qryNextInvoice LEFT JOIN Table2 ON qryNextInvoice.NextInv = Table2.InvoiceNumber
WHERE (((Table2.InvoiceNumber) Is Null));


This works well for "single" missing invoices, but with big clumbs of missing only the first one is reported

 

[Chrisopia]

worked a treat - together with an "Is Null" statement it filtered out all the ones that were there and left me with a list of number... Just what I needed Thanks!

 

[rainman89]

Like i said... Mine is Ugly, but it will handle clumps!!

Thanks mailman!