View that returns a range of numbers

M

mar

I'd like to create a view that returns numbers 1 through 364. Ex:
NUMBER
1
2
3
.....

The only way that I know how to do this is by using UNION:

(SELECT 1 AS NUMBER UNION SELECT 2 AS NUMBER UNION SELECT 3 AS NUMBER)
AS ONETO364

But that means I'd have to create 364 unions. Is there a shorter
script for this? I'm trying to avoid creating a table and populating
it with 364 rows.

Thanks.
 
M

mar

Here's one other option I googled...

SELECT TOP 364
(select SUM(1)
from TABLE
where TABLEID<= A1.TABLEID) as 'NUMBER'
FROM TABLE A1
ORDER BY NUMBER
 
M

Marshall Barton

mar said:
I'd like to create a view that returns numbers 1 through 364. Ex:
NUMBER
1
2
3
....

The only way that I know how to do this is by using UNION:

(SELECT 1 AS NUMBER UNION SELECT 2 AS NUMBER UNION SELECT 3 AS NUMBER)
AS ONETO364

But that means I'd have to create 364 unions. Is there a shorter
script for this? I'm trying to avoid creating a table and populating
it with 364 rows.
Click to expand...


Create the table. A table of numbers like that is useful in
many scenarios (I have one in every database).

If you want to get tricky, you can get by with a ten row
table with rows 0,1, ...,9 The query would then be like:

SELECT 100 * Hundreds.Num + 10 & Tens.Num + Units.Num
FROM Numbers As Hundreds, Numbers As Tens, Numbers As Units
WHERE 100 * Hundreds.Num + 10 & Tens.Num + Units.Num <= 364
 
J

John W. Vinson

I'd like to create a view that returns numbers 1 through 364. Ex:
NUMBER
1
2
3
....

The only way that I know how to do this is by using UNION:

(SELECT 1 AS NUMBER UNION SELECT 2 AS NUMBER UNION SELECT 3 AS NUMBER)
AS ONETO364

But that means I'd have to create 364 unions. Is there a shorter
script for this? I'm trying to avoid creating a table and populating
it with 364 rows.

Thanks.
Click to expand...

Well, that's three minutes work if you use Excel: open a new spreadsheet; type
1 in A1; select A1..A364 (or A10000 if you like); Insert... Fill Series. Then
copy and paste.

FWIW I will routinely put a table named Num, single field N, values 0 through
10000 into a table. This can be used in queries whenever a series like this is
required.

Also for what it's worth, your UNION would certainly fail with a Query Too
Complex error in Access. :-{(
 
M

Marshall Barton

mar said:
Here's one other option I googled...

SELECT TOP 364
(select SUM(1)
from TABLE
where TABLEID<= A1.TABLEID) as 'NUMBER'
FROM TABLE A1
ORDER BY NUMBER
Click to expand...


You should be careful choosing a table to use for that kind
of thing. It won't work if the table has less than 364
records or if there are duplicate ID values and it might be
noticably slow on a very large table.

You really should take a break from the analysis and just
create a numbers table.

If you are using A2002 or A2003, it;s a trivial exercise.
Create a new table using datasheet view. Enter 1 in the
first row, 2 in the second row and then hold down the down
arrow key for a couple of minutes.
 
Top