CODEWITHSUNDEEP
sqlms-access
SSK
SSK

Reputation: 803

How to find missing records for a given range?

I have a following table as below

Country  Level      Code
USA      Level A    10
USA      Level A    11
USA      Level A    12
USA      Level A    13
USA      Level A    14
USA      Level B    20
USA      Level B    21
USA      Level B    22
USA      Level B    23
USA      Level B    24

and

Level      Min Code     Max Code
Level A    10           15
Level B    20           25

I need to find if Table 1 has all the codes between the range defined in Table 2, if some thing is missing, i want that to an output of a query. Please help

In case of above example Level A 15, Level B 25 is missing

Upvotes: 1

Views: 1976

Answers (2)

Gord Thompson
Gord Thompson

Reputation: 123484

This is a class of problem that can be solved with Access SQL by using a "numbers table". This "numbers table" is a table you manually create in Access that contains a column of numeric values (usually sequential) covering a range of values that you will be working with.

(Other more sophisticated implementations of the SQL language allow us to create this sort of table "on the fly", but unfortunately Access SQL doesn't support that so we have to create the table beforehand, either manually or via some VBA code.)

For this example we'll use a table named [Numbers] with a single Number (Long Integer) field named [n]. We need it to cover the entire expected range of [Code] values, 10 through 25, so our [Numbers] table will look like this:

 n
--
10
11
12
...
24
25

(Note that it usually doesn't matter if a "numbers table" contains values outside expected range, since the queries that use them often limit the range of values in a WHERE clause someplace, but it is imperative that the "numbers table" completely covers the expected range of values.)

Once we have created and populated our [Numbers] table we can start with the query

SELECT c.Country, c.Level, Numbers.n AS Code
FROM 
Numbers,
(SELECT DISTINCT [Country], [Level] FROM Codes) c 
INNER JOIN 
CodeRanges cr 
    ON c.Level=cr.Level
WHERE Numbers.n BETWEEN cr.[Min Code] AND cr.[Max Code]

which returns all possible codes within the prescribed ranges

Country  Level    Code 
-------  -------  ----
USA      Level A    10
USA      Level A    11
USA      Level A    12
USA      Level A    13
USA      Level A    14
USA      Level A    15
USA      Level B    20
USA      Level B    21
USA      Level B    22
USA      Level B    23
USA      Level B    24
USA      Level B    25

Now we can find the items in the [Codes] table that are missing from that list by using the above query as a derived table (which I will call "AllCodes"):

SELECT [Country], [Level], [Code]
FROM 
    (
        SELECT c.Country, c.Level, Numbers.n AS Code
        FROM 
        Numbers,
        (SELECT DISTINCT [Country], [Level] FROM Codes) c 
        INNER JOIN 
        CodeRanges cr 
            ON c.Level=cr.Level
        WHERE Numbers.n BETWEEN cr.[Min Code] AND cr.[Max Code]
    ) AllCodes
WHERE NOT EXISTS
    (
        SELECT * FROM Codes
        WHERE Codes.Country=AllCodes.Country 
            AND Codes.Level=AllCodes.Level 
            AND Codes.Code=AllCodes.Code
    )

which returns

Country  Level    Code
-------  -------  ----
USA      Level A    15
USA      Level B    25

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269693

Generating the missing codes is a real pain, especially in Access. You need a list of all possible codes, and then find the ones that are not in the range. Generating such a list is not trivial in Access.

To find if anything is missing, you can just count:

select r.*
from ranges r left join
     (select level, count(distinct code) as NumCodes
      from t
     ) t 
     on r.level = t.level
where t.NumCodes < (r.MaxCode - r.MinCode)+1

Upvotes: 1

Related Questions