View that returns a range of numbers

[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.

 

[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

 

[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.

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. :-{(