Hi Tomasz,
if you have a table with 255 records numbered from 1 to 255
try it
----
SELECT tblNumbers.Number
FROM tblNumbers LEFT JOIN tblRanges
ON tblNumbers.Number>=tblRanges.From
AND tblNumbers.Number<=tblRanges.To
WHERE tblRanges.From IS NULL
ORDER BY tblNumbers.Number
----
if you don't want to have the table write this query
----
SELECT U.Nr+D.Nr+C.Nr AS [Number]
FROM (
SELECT TOP 1 0 AS Nr
FROM MSysObjects
UNION ALL
SELECT TOP 1 1
FROM MSysObjects
UNION ALL
SELECT TOP 1 2
FROM MSysObjects
UNION ALL
SELECT TOP 1 3
FROM MSysObjects
UNION ALL
SELECT TOP 1 4
FROM MSysObjects
UNION ALL
SELECT TOP 1 5
FROM MSysObjects
UNION ALL
SELECT TOP 1 6
FROM MSysObjects
UNION ALL
SELECT TOP 1 7
FROM MSysObjects
UNION ALL
SELECT TOP 1 8
FROM MSysObjects
UNION ALL
SELECT TOP 1 9
FROM MSysObjects) AS U,
(SELECT TOP 1 10 AS Nr
FROM MSysObjects
UNION ALL
SELECT TOP 1 0
FROM MSysObjects
UNION ALL
SELECT TOP 1 20
FROM MSysObjects
UNION ALL
SELECT TOP 1 30
FROM MSysObjects
UNION ALL
SELECT TOP 1 40
FROM MSysObjects
UNION ALL
SELECT TOP 1 50
FROM MSysObjects
UNION ALL
SELECT TOP 1 60
FROM MSysObjects
UNION ALL
SELECT TOP 1 70
FROM MSysObjects
UNION ALL
SELECT TOP 1 80
FROM MSysObjects
UNION ALL
SELECT TOP 1 90
FROM MSysObjects) AS D,
(
SELECT TOP 1 0 AS Nr
FROM MSysObjects
UNION ALL
SELECT TOP 1 100
FROM MSysObjects
UNION ALL
SELECT TOP 1 200
FROM MSysObjects
) AS C
WHERE U.Nr+D.Nr+C.Nr Between 1 AND 255
----
save the query with name qryNumbers
then write the final query
----
SELECT qryNumbers.Number
FROM qryNumbers LEFT JOIN tblRanges
ON qryNumbers.Number>=tblRanges.From
AND qryNumbers.Number<=tblRanges.To
WHERE tblRanges.From IS NULL
ORDER BY qryNumbers.Number
----
bye
--
Giorgio Rancati
[Office Access MVP]
Hello.
4 example
There is the table, each record of this table has two values named
"from" and "to".
The goal is to write the query which displays values from 1 to 255
without values from the ranges from the table.
Example
Range1:
From= 5
To= 10
Range2:
From= 15
To= 254
The result will looks like:
Thank you very much in advance
Best regards
