Microsoft Access / SQL - determine missing IDs

LeighWardle
LeighWardle used Ask the Experts™
on
Hi Experts,

I am looking to determine the missing IDs (I call them Licence Numbers) in the range 26,000 to 30,000.

Here are my 1688 used IDs (so should be about 2312 missing IDs:

ID
26001
26002
26003
26004
26005
26006
26007
26008
26009
26010
26011
26012
26013
26014
26015
26016
26017
26018
26019
26020
26021
26022
26023
26024
26025
26026
26027
26028
26029
26030
26031
26032
26033
26034
26035
26036
26037
26038
26039
26040
26041
26042
26043
26044
26045
26046
26047
26048
26049
26050
26051
26052
26053
26054
26055
26056
26057
26058
26059
26060
26061
26062
26063
26064
26065
26066
26067
26070
26071
26072
26073
26074
26075
26076
26077
26078
26079
26080
26081
26082
26083
26084
26085
26086
26087
26088
26089
26090
26091
26092
26093
26094
26095
26096
26097
26098
26099
26100
26101
26102
26103
26104
26105
26106
26107
26108
26109
26110
26111
26112
26113
26114
26115
26116
26117
26118
26119
26120
26121
26122
26123
26124
26125
26126
26127
26128
26129
26130
26131
26132
26133
26134
26135
26136
26137
26138
26139
26140
26141
26142
26143
26144
26145
26146
26147
26148
26149
26150
26151
26152
26153
26154
26155
26156
26157
26158
26159
26160
26161
26162
26163
26164
26165
26166
26167
26168
26169
26170
26171
26172
26173
26174
26175
26176
26177
26178
26179
26180
26181
26182
26183
26184
26185
26186
26187
26188
26189
26190
26191
26192
26193
26194
26195
26196
26197
26198
26199
26200
26201
26202
26203
26204
26205
26206
26207
26208
26209
26210
26211
26212
26213
26214
26215
26216
26217
26218
26219
26220
26221
26222
26223
26224
26225
26226
26227
26228
26229
26230
26231
26232
26233
26234
26235
26236
26237
26238
26239
26241
26242
26243
26244
26245
26246
26247
26248
26249
26250
26251
26252
26253
26254
26255
26257
26258
26259
26260
26261
26262
26263
26264
26265
26266
26267
26268
26270
26271
26272
26273
26274
26275
26276
26277
26278
26279
26285
26286
26287
26288
26289
26290
26291
26292
26293
26294
26295
26296
26297
26298
26299
26300
26301
26302
26303
26304
26305
26306
26307
26308
26309
26310
26311
26312
26313
26314
26315
26316
26317
26318
26319
26320
26321
26322
26323
26324
26325
26326
26327
26328
26329
26330
26331
26332
26333
26334
26335
26336
26337
26338
26339
26340
26341
26342
26343
26344
26345
26346
26347
26348
26349
26350
26351
26352
26353
26354
26355
26356
26357
26358
26359
26360
26361
26362
26363
26364
26365
26366
26367
26368
26369
26370
26371
26372
26373
26374
26375
26376
26377
26378
26379
26380
26381
26382
26383
26384
26385
26387
26388
26389
26390
26391
26392
26393
26394
26395
26396
26397
26398
26399
26400
26401
26402
26403
26404
26405
26406
26407
26408
26409
26410
26411
26412
26413
26414
26415
26416
26417
26418
26419
26420
26421
26422
26423
26424
26425
26426
26427
26428
26429
26430
26431
26432
26433
26434
26435
26436
26437
26438
26439
26440
26441
26442
26443
26444
26445
26446
26447
26448
26449
26450
26451
26452
26453
26454
26456
26457
26458
26459
26460
26461
26462
26463
26464
26465
26466
26467
26468
26469
26470
26471
26472
26473
26474
26475
26476
26477
26478
26479
26480
26481
26482
26483
26484
26485
26486
26487
26488
26489
26490
26491
26492
26493
26494
26495
26496
26497
26498
26499
26500
26501
26502
26503
26505
26506
26507
26508
26509
26510
26511
26512
26513
26514
26515
26516
26517
26518
26519
26520
26521
26522
26523
26524
26525
26526
26527
26528
26529
26530
26531
26532
26533
26534
26535
26536
26537
26538
26539
26540
26541
26542
26543
26544
26545
26546
26547
26548
26549
26550
26551
26552
26553
26554
26555
26556
26557
26558
26559
26560
26561
26562
26563
26564
26566
26567
26568
26569
26570
26571
26572
26573
26574
26575
26576
26577
26578
26579
26580
26581
26582
26583
26584
26586
26587
26588
26589
26590
26591
26592
26593
26594
26595
26596
26597
26598
26599
26600
26601
26602
26603
26604
26605
26606
26607
26608
26609
26610
26611
26612
26613
26614
26615
26616
26617
26618
26619
26620
26621
26622
26623
26624
26625
26626
26627
26628
26629
26630
26631
26632
26634
26635
26636
26637
26638
26639
26640
26641
26642
26643
26644
26645
26646
26647
26648
26649
26650
26651
26652
26653
26654
26655
26656
26657
26658
26659
26660
26661
26662
26663
26664
26665
26667
26668
26670
26671
26672
26673
26674
26675
26676
26677
26678
26679
26680
26681
26682
26683
26684
26685
26686
26687
26688
26689
26690
26691
26692
26693
26694
26695
26696
26697
26698
26699
26701
26702
26703
26704
26705
26706
26707
26708
26709
26710
26711
26712
26713
26714
26715
26716
26717
26718
26719
26720
26721
26722
26723
26724
26725
26726
26727
26728
26729
26730
26731
26732
26733
26734
26735
26736
26737
26738
26739
26740
26741
26742
26743
26744
26745
26746
26747
26748
26749
26750
26751
26752
26753
26754
26755
26756
26757
26758
26759
26760
26761
26763
26764
26765
26766
26767
26768
26769
26770
26771
26772
26773
26774
26775
26776
26777
26778
26779
26780
26781
26782
26783
26784
26785
26786
26787
26788
26789
26790
26791
26792
26793
26794
26795
26796
26797
26798
26799
26800
26801
26802
26803
26804
26805
26806
26807
26808
26809
26810
26811
26812
26813
26814
26815
26816
26817
26818
26819
26820
26821
26822
26823
26824
26825
26826
26827
26828
26829
26830
26831
26832
26833
26834
26835
26836
26837
26838
26839
26840
26841
26842
26843
26844
26845
26846
26847
26848
26849
26850
26851
26852
26853
26854
26856
26857
26858
26859
26860
26861
26862
26863
26864
26865
26866
26867
26868
26869
26870
26871
26872
26873
26874
26875
26876
26877
26878
26879
26880
26881
26882
26883
26884
26885
26886
26887
26888
26889
26890
26891
26892
26893
26894
26895
26896
26897
26898
26899
26900
26901
26902
26903
26904
26905
26906
26907
26908
26909
26910
26911
26912
26913
26914
26915
26916
26917
26918
26919
26920
26921
26922
26923
26924
26925
26926
26927
26928
26929
26930
26931
26932
26933
26934
26935
26936
26937
26938
26939
26940
26941
26942
26943
26944
26945
26946
26947
26948
26949
26950
26951
26952
26953
26954
26955
26956
26957
26958
26959
26960
26961
26962
26963
26964
26965
26966
26967
26968
26969
26970
26971
26972
26973
26974
26975
26976
26977
26978
26979
26980
26981
26982
26983
26984
26985
26986
26987
26988
26989
26990
26991
26992
26993
26994
26995
26996
26997
26998
26999
27001
27002
27003
27004
27005
27018
27019
27020
27021
27022
27035
27036
27037
27038
27052
27053
27055
27056
27069
27070
27071
27072
27073
27086
27087
27088
27089
27090
27103
27104
27106
27120
27121
27122
27123
27137
27138
27139
27140
27141
27155
27157
27158
27172
27173
27174
27188
27189
27190
27191
27192
27193
27194
27195
27205
27206
27207
27208
27209
27223
27224
27225
27239
27240
27241
27242
27243
27256
27257
27258
27260
27273
27274
27275
27276
27277
27290
27291
27292
27294
27307
27308
27309
27310
27311
27324
27325
27326
27328
27342
27344
27345
27358
27359
27360
27361
27362
27375
27376
27377
27378
27379
27392
27393
27394
27395
27396
27409
27410
27411
27412
27413
27426
27427
27428
27429
27430
27443
27444
27445
27446
27447
27460
27461
27462
27463
27464
27477
27478
27479
27481
27494
27496
27497
27498
27511
27512
27513
27514
27515
27529
27530
27531
27532
27546
27547
27548
27549
27562
27563
27564
27565
27566
27579
27580
27582
27583
27596
27597
27598
27599
27600
27613
27614
27615
27616
27617
27630
27631
27632
27633
27634
27647
27648
27649
27650
27651
27664
27666
27667
27668
27681
27682
27683
27684
27685
27698
27699
27700
27701
27702
27715
27716
27717
27718
27719
27733
27734
27735
27736
27749
27750
27751
27752
27753
27766
27769
27770
27783
27784
27785
27787
27801
27803
27804
27817
27818
27819
27820
27821
27834
27835
27836
27837
27838
27851
27852
27853
27854
27855
27869
27870
27871
27872
27885
27886
27887
27888
27889
27902
27903
27904
27905
27906
27919
27920
27921
27922
27923
27936
27937
27938
27939
27940
27953
27954
27955
27956
27957
27970
27971
27972
27973
27974
27987
27988
27989
27990
27991
28004
28006
28007
28008
28021
28022
28023
28024
28025
28038
28039
28041
28042
28056
28057
28058
28059
28073
28074
28075
28076
28089
28091
28092
28093
28106
28108
28109
28110
28123
28125
28126
28127
28140
28142
28143
28144
28157
28158
28159
28160
28161
28174
28175
28176
28177
28178
28191
28192
28193
28194
28195
28208
28209
28210
28211
28212
28225
28226
28227
28228
28229
28242
28243
28244
28245
28246
28260
28261
28262
28263
28276
28277
28278
28279
28280
28294
28295
28296
28297
28310
28311
28312
28313
28314
28327
28328
28329
28330
28331
28345
28346
28348
28361
28363
28365
28378
28379
28380
28381
28382
28396
28397
28399
28412
28413
28414
28415
28416
28429
28430
28431
28432
28433
28446
28447
28448
28449
28450
28465
28480
28481
28482
28483
28497
28498
28499
28500
28514
28515
28516
28517
28531
28532
28533
28534
28548
28549
28550
28551
28565
28566
28567
28568
28582
28583
28585
28599
28600
28601
28602
28616
28617
28618
28619
28633
28634
28635
28636
28650
28651
28652
28653
28667
28668
28669
28670
28684
28686
28687
28701
28702
28703
28704
28718
28719
28720
28721
28735
28736
28737
28738
28752
28753
28754
28755
28769
28770
28771
28772
28786
28787
28788
28803
28804
28806
28820
28821
28823
28837
28838
28839
28840
28854
28855
28856
28857
28871
28872
28874
28888
28889
28890
28891
28905
28906
28908
28922
28923
28925
28939
28940
28942
28956
28957
28958
28959
28973
28974
28975
28976
28990
28991
28993
29008
29009
29010
29025
29026
29027
29041
29042
29043
29044
29058
29059
29060
29061
29075
29076
29077
29078
29092
29093
29094
29095
29109
29111
29112
29129
29143
29144
29145
29146
29160
29161
29162
29163
29164
29177
29178
29179
29180
29194
29195
29196
29197
29211
29212
29213
29228
29229
29230
29231
29245
29246
29247
29248
29262
29263
29264
29265
29279
29280
29281
29282
29296
29298
29313
29315
29316
29330
29331
29332
29333
29349
29350
29366
29367
29381
29383
29384
29398
29399
29400
29401
29415
29416
29417
29418
29432
29433
29434
29435
29449
29450
29451
29452
29467
29468
29469
29484
29485
29486
29500
29501
29502
29503
29517
29518
29519
29520
29534
29536
29537
29553
29554
29568
29569
29570
29571
29585
29586
29587
29602
29603
29604
29605
29619
29620
29621
29622
29636
29637
29638
29639
29653
29655
29656
29670
29672
29673
29687
29688
29689
29690
29704
29705
29706
29707
29721
29722
29723
29724
29738
29739
29740
29741
29755
29756
29757
29758
29773
29774
29775
29789
29790
29791
29792
29806
29807
29808
29809
29824
29825
29826
29840
29841
29842
29843
29857
29858
29859
29860
29874
29875
29876
29877
29892
29893
29894
29908
29909
29910
29924
29925
29927
29928
29942
29943
29959
29961
29962
29976
29977
29978
29993
29994
29995
29998
29999

Open in new window


Regards,
Leigh
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Software & Systems Engineer
Commented:
From the query that return those 1688 IDS you have to modify it like this
SELECT ID FROM THESOURCETABLE WHERE ID NOT IN (SELECT ID FROM YOURORIGINALQUERY)
Fabrice LambertConsulting
Distinguished Expert 2017

Commented:
Hi,

Depend on the size of your tables, It might be faster to do a left or right join:
SELECT SourceTable.ID
FROM SourceTable LEFT JOIN TargetTable
WHERE TargetTable.ID IS NULL;

Open in new window

Mark EdwardsChief Technology Officer
Commented:
As for checking a list with missing sequential numbers between a range, the process would need to know what you are wanting to compare your list to as well as the upper and lower limits of the range.

A simple mismatched IDs query like the ones proposed above would work IF you have a full list of IDs that you can compare to the list that has the missing ones, but there-in lies the trick - IF you have a full list of IDs from 2600 to 30000.  If you don't, then you'll need to create one to use in the mismatched query.

As for a process that will do it WITHOUT a full comparison list.... well... maybe some one has something that will do that.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Fabrice LambertConsulting
Distinguished Expert 2017
Commented:
Mistake on my previous post:
SELECT SourceTable.ID
FROM SourceTable LEFT JOIN TargetTable
         ON SourceTable.ID = TargetTable.ID
WHERE TargetTable.ID IS NULL;

Open in new window

Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Here's a simple and fast method using a collection to fill a listbox:

Find missing numbers in a sequence

Author

Commented:
Thanks everyone for your help.
Regards,
Leigh

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial