'how to find missing number in a row in ms access

I have the below series:

REF NO
AI951
AI952
AI953
AI954
AI955
AI956
AI957
AI958
AI959
AI960
AI961
AI962
AI963
AI965
AI966
AI967
AI968
AI970
AI971
AI972

In above column series, AI964 and AI969 are missing.

To find those missing numbers in a row sequence in Access, what is the process? Do I have to write a query, or is there any predesigned tool?

sqlms-accessms-access-2010ms-access-2007


Solution 1:[1]

First, create a Cartesian (multiplying) query to generate all the numbers:

Select
    "AI" & CStr((Select Min(Val(Mid([REF NO],3))) From Samples) + [T].[Factor]) As RefNo
From
    (Select Distinct 
        [Tens]+[Ones] As Factor, 
        10*Abs([Deca].[id] Mod 10) As Tens, 
        Abs([Uno].[id] Mod 10) As Ones 
    From
        MSysObjects As Uno, 
        MSysObjects As Deca)  As T
Where
    (Select Min(Mid([REF NO],3)) From Samples) + [T].[Factor] <= (Select Max(Val(Mid([REF NO],3))) From Samples)

Save this as AllSamples.

Next, create a query to list the missing numbers:

Select 
    AllSamples.RefNo As [REF NO]
From 
    AllSamples 
Left Join 
    Samples 
    On AllSamples.RefNo = Samples.[REF NO]
Where 
    Samples.[REF NO] Is Null

Output:

enter image description here

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Gustav

Related Questions