Find missing numbers

A

AHopper

I want to display/print missing numbers in a series.

Table has fields "JobNumber" "SkidNumber" "UniqueNumber"
and "Quantity"
JobNumber SkidNumber UniqueNumber Quantity
211 200 00001 2000
211 201 00001 2000
211 201 00002 4000
211 201 00003 4000
211 201 00006 4000
211 201 00007 500
211 250 00007 3500
211 250 00008 4000
211 250 00010 4000

First I want to eliminate any duplicate UniqueNumbers
so in the case above I would end up with
00001,00002,00003,00006,00007,00008,00010 as a list
Next I want to list the numbers that are missing,-
00004,00005,00009.
If the user defines a range like 00001 to 00015 the
missing numbers would be
00004,00005,00009,00011,00012,00013,00014,00015.
Finally I would like the results to display on screen
and/or print as a report (both if possible) as the user
would choose.

I would appreciate any help in how to do this.

Thanks
Allan
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Missing Numbers display/print 0
Query help 1
Numbering Date 10
Query to combine records based on matching criteria. PLEASE help. 10
Selsct Query Problem 3
Select Query 6
how to track a change in a cell content 1
Invoice Numbers 1

Top