Set Complement and Temporary Tables

[JohnCreighton_]

I understand SQL is a set language and the Jet engine is based on SQL.
I was wondering if there was a shorthand for defining ranges of numbers
so I could do things like return all integers say between 1 and 100
that aren't in a given field. Obviously If I already had a table of
such integers I could do this but that would seem like a waste of
memory. Maybe a temporary table would also waste memory but at at least
I would know how much memory I need to use a priori and it would only
use memory during the query.

 

[John Vinson]

Obviously If I already had a table of
such integers I could do this but that would seem like a waste of
memory.

Disk is cheap. A table with numbers 1 to 10000 takes about 80kbytes
(40 for the table, 40 for the index) plus a little bit of overhead; it
doesn't take ANY memory, except when it's being used. You can have
2,000 kbytes in a .mdb file - a Numbers table is very useful and very
cheap.

John W. Vinson[MVP]

 

[JohnCreighton_]

Disk is cheap. A table with numbers 1 to 10000 takes about 80kbytes
(40 for the table, 40 for the index) plus a little bit of overhead; it
doesn't take ANY memory, except when it's being used. You can have
2,000 kbytes in a .mdb file - a Numbers table is very useful and very
cheap.

John W. Vinson[MVP]

That is a good point. I'll consider that in the future. Anyway I came
up with an alternative way of finding missing numbers:

SELECT summary_index.id, summary_index_1.id
FROM summary_index
LEFT JOIN summary_index As summary_index_1
ON summary_index.id+1=summary_index_1.id
Where ISNULL(summary_index_1.id)

This query finds the start of the interval of missing numbers in the
query or table summary_index. A similar query could be constructed to
find the end point. I would still like to know if there are short hand
ways to generate a sequence or series table incase I need it in the
future.