I have a db of numbers. How do I find numbers that are not there?

E-man · Oct 11, 2004

I have a db with 87000 records. Each record has a field with a number in it
in sequence. However there are about 20000 records that are not represented.
How do I find those numbers?
IE:
Say I have 1, 2, 4, 8, 9 in the db. How do I figure out the missing numbers
3, 5, 6, 7?

John Vinson · Oct 11, 2004

I have a db with 87000 records.

Jargon alert: you have a *table* with 87000 records, presumably in a
Database which contains other objects.

Each record has a field with a number in it
in sequence. However there are about 20000 records that are not represented.
How do I find those numbers?
IE:
Say I have 1, 2, 4, 8, 9 in the db. How do I figure out the missing numbers
3, 5, 6, 7?

Simplest would be to construct a table with 100000 records with all
values. A sneaky way to do this is to manually construct a table Num,
with one field N; manually fill it with ten records, values 0 to 9.

Then create a Query

SELECT Num.N + Num_1.N * 10 + Num_2.N * 100 + Num_3.N * 1000 + Num_4.N
* 10000
FROM Num, Num AS Num_1, Num AS Num_2, Num AS Num_3, Num AS Num_4

Save this query as AllNum.

Then use the "Unmatched Query Wizard" to find all records in AllNum
which do not have matches in your table.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

E-man · Oct 12, 2004

John,

Thanks. I did the manual thing and it worked great. Now here is where it
gets wierd:
The table has 3 fields; Region, Record#, and Year. I want to find the
missing Record #'s for each Region for each Year.

E

John Vinson · Oct 13, 2004

Thanks. I did the manual thing and it worked great. Now here is where it
gets wierd:
The table has 3 fields; Region, Record#, and Year. I want to find the
missing Record #'s for each Region for each Year.

You'll need to explain this. If the record is missing, what are its
Region and Year? How could you possibly know?

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

Tom Wickerath · Oct 13, 2004

Cool example, John.

Thanks for posting it.
___________________________________

I have a db with 87000 records.

Jargon alert: you have a *table* with 87000 records, presumably in a
Database which contains other objects.

Each record has a field with a number in it
in sequence. However there are about 20000 records that are not represented.
How do I find those numbers?
IE:
Say I have 1, 2, 4, 8, 9 in the db. How do I figure out the missing numbers
3, 5, 6, 7?

Simplest would be to construct a table with 100000 records with all
values. A sneaky way to do this is to manually construct a table Num,
with one field N; manually fill it with ten records, values 0 to 9.

Then create a Query

SELECT Num.N + Num_1.N * 10 + Num_2.N * 100 + Num_3.N * 1000 + Num_4.N
* 10000
FROM Num, Num AS Num_1, Num AS Num_2, Num AS Num_3, Num AS Num_4

Save this query as AllNum.

Then use the "Unmatched Query Wizard" to find all records in AllNum
which do not have matches in your table.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

E-man · Oct 13, 2004

John,

I think only by running the below query against each year within a zone. Any
ideas on automation instead of manually creating a table for each zone (about
30)?

Eric

John Vinson · Oct 13, 2004

John,

I think only by running the below query against each year within a zone. Any
ideas on automation instead of manually creating a table for each zone (about
30)?

Not necessary at all. You can use a Query to do this; at the very
worst you'ld have thirty *queries*, one for each zone - but even that
should not be needed.

I'm not sure I understand though. You have numbers from 1 to 87000,
with some gaps... right? Should each zone have 87000 records every
year? Or do some zones have one subset of the numbers, other zones a
different subset? What constitutes a "missing" number?

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

E-man · Oct 13, 2004

John,

There are 87000 records in the table. Each year in each region has record
numbers starting at 1.

Let's say I have the following fields:
Region Record# Year
West 1 01
West 3 01

I want to know that West 2 01 is missing.

Eric

John Vinson · Oct 14, 2004

John,

There are 87000 records in the table. Each year in each region has record
numbers starting at 1.

Let's say I have the following fields:
Region Record# Year
West 1 01
West 3 01

I want to know that West 2 01 is missing.

ok... you'll want to (for efficiency) alter the definition of AllNum;
your Record# field will never come close to 100000. Chop off one or
two levels and this query will run faxter.

Try copying this query into the SQL window of a new query:

SELECT Y.Region, Y.[Year], AllNum.N
FROM Yourtablename AS Y RIGHT JOIN AllNum
ON AllNum.N = Y.[Record#]
WHERE AllNum.N <= (SELECT Max([Record#] FROM yourtablename AS Z
WHERE Z.Region = Y.Region AND Z.[Year] = Y.[Year])
AND Y.[Record#] IS NULL;

substituting your table name of course.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

E-man · Oct 14, 2004

John,

I get the following error:
Missing ), ] or Item in query expression 'AllNum.N <= (SELECT Max([Record#]
FROM yourtablename AS Z
WHERE Z.Region = Y.Region AND Z.[Year] = Y.[Year])
AND Y.[Record#] IS NULL;'.

Eric

John Vinson said:
John,

There are 87000 records in the table. Each year in each region has record
numbers starting at 1.

Let's say I have the following fields:
Region Record# Year
West 1 01
West 3 01

I want to know that West 2 01 is missing.

Click to expand...

ok... you'll want to (for efficiency) alter the definition of AllNum;
your Record# field will never come close to 100000. Chop off one or
two levels and this query will run faxter.

Try copying this query into the SQL window of a new query:

SELECT Y.Region, Y.[Year], AllNum.N
FROM Yourtablename AS Y RIGHT JOIN AllNum
ON AllNum.N = Y.[Record#]
WHERE AllNum.N <= (SELECT Max([Record#] FROM yourtablename AS Z
WHERE Z.Region = Y.Region AND Z.[Year] = Y.[Year])
AND Y.[Record#] IS NULL;

substituting your table name of course.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

Douglas J. Steele · Oct 14, 2004

That should be

....(SELECT Max([Record#]) FROM...

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

E-man said:
John,

I get the following error:
Missing ), ] or Item in query expression 'AllNum.N <= (SELECT Max([Record#]
FROM yourtablename AS Z
WHERE Z.Region = Y.Region AND Z.[Year] = Y.[Year])
AND Y.[Record#] IS NULL;'.

Eric

John Vinson said:

John,

There are 87000 records in the table. Each year in each region has record
numbers starting at 1.

Let's say I have the following fields:
Region Record# Year
West 1 01
West 3 01

I want to know that West 2 01 is missing.

Click to expand...

ok... you'll want to (for efficiency) alter the definition of AllNum;
your Record# field will never come close to 100000. Chop off one or
two levels and this query will run faxter.

Try copying this query into the SQL window of a new query:

SELECT Y.Region, Y.[Year], AllNum.N
FROM Yourtablename AS Y RIGHT JOIN AllNum
ON AllNum.N = Y.[Record#]
WHERE AllNum.N <= (SELECT Max([Record#] FROM yourtablename AS Z
WHERE Z.Region = Y.Region AND Z.[Year] = Y.[Year])
AND Y.[Record#] IS NULL;

substituting your table name of course.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

Click to expand...

E-man · Oct 14, 2004

John,

The Query comes up blank after running a couple of hours.

Eric

John Vinson said:
John,

There are 87000 records in the table. Each year in each region has record
numbers starting at 1.

Let's say I have the following fields:
Region Record# Year
West 1 01
West 3 01

I want to know that West 2 01 is missing.

Click to expand...

ok... you'll want to (for efficiency) alter the definition of AllNum;
your Record# field will never come close to 100000. Chop off one or
two levels and this query will run faxter.

Try copying this query into the SQL window of a new query:

SELECT Y.Region, Y.[Year], AllNum.N
FROM Yourtablename AS Y RIGHT JOIN AllNum
ON AllNum.N = Y.[Record#]
WHERE AllNum.N <= (SELECT Max([Record#] FROM yourtablename AS Z
WHERE Z.Region = Y.Region AND Z.[Year] = Y.[Year])
AND Y.[Record#] IS NULL;

substituting your table name of course.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

John Vinson · Oct 15, 2004

John,

The Query comes up blank after running a couple of hours.

Then either there is some error I cannot detect in the query (though I
admit I haven't recreated your table to test it), or there are no
gaps.

Here's an alternative. It won't find all the missing numbers but it
will find the last missing number in each block of missing numbers,
and it will run a lot faster; you can create a unique Index on [Year],
[Region] and [Record#] to make it *very* fast:

SELECT Y.Region, Y.[Year], Y.[Record#] - 1
FROM Yourtablename AS X LEFT JOIN yourtablename AS Y
ON X.[Year] = Y.[Year] AND X.[Region] = Y.[Region]
AND X.[Record#] = Y.[Record#] - 1
WHERE Y.[Record#] IS NULL;

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

I have a db of numbers. How do I find numbers that are not there?

E-man

John Vinson

E-man

John Vinson

Tom Wickerath

E-man

John Vinson

E-man

John Vinson

E-man

Douglas J. Steele

E-man

John Vinson