challenge with generating a range of sequential numbers

[David Mueller]

I can make this work but I'm fishing for a more elegant solution - one that
does not use a table.

A. I need to generate a range of consecutive numbers from thin air. From 0
to 99999. Example: 0, 1, 2, 3, ... 99997, 99998, 99999.

B. I'm using a table that holds values for the Ones place, Tens Place,
etc... up to Ten Thousands. 10 records for the "ones" place, 0 through 9. 10
records for the "tens" place, 0, 10, 20, ... 90. Etc. and so on for hundreds,
thousands, ...

I tried using a bunch of union queries in place of those 5 [SELECT...]
statements in the FROM clause, but I can't seem to make that work.

Thanks,
David


--- this is the SQL I'm using ---

SELECT [Ones]+[tens]+[hundreds]+[thousands]+[tenthousands] AS MyNum FROM

[select numbers as Ones from tbl_numbers where place_value = 1]. as
OnesPlace,
[select numbers as Tens from tbl_numbers where place_value = 10]. as
TensPlace ,
[select numbers as Hundreds from tbl_numbers where place_value = 100]. as
HundredsPlace,
[select numbers as Thousands from tbl_numbers where place_value = 1000]. as
ThousandsPlace,
[select numbers as TenThousands from tbl_numbers where place_value = 10000].
as TenThounsandsPlace

 

[Duane Hookom]

You can create a single table tblNums with a numeric field [Num] and values 0
- 9.
Then create a cartesian query with SQL like:

SELECT
[Ones].[Num]+[Tens].[Num]*10+[Hundreds].[Num]*100+[Thousands].[Num]*1000+[TenThousands].[Num]*10000 AS Expr1
FROM tblNums AS Ones, tblNums AS Tens, tblNums AS Hundreds, tblNums AS
Thousands, tblNums AS TenThousands;

This example creates numbers 0 - 99999. You can add in more copies of the
same table to up the maximum number.

 

[John Spencer]

One table, one field (iota) with 10 records and values 0 to 9.

Use that table in a cartesian join.

SELECT TableNum.Iota + Tens.Iota*10 + Hundreds*100 + Thousands*1000
FROM TableNum, TableNum as Tens, TableNum as Hundreds, TableNum as Thousands
WHERE TableNum.Iota + Tens.Iota*10 + Hundreds*100 + Thousands*1000
<= [Enter upper Limit]

Or if you want to specify a range
SELECT TableNum.Iota + Tens.Iota*10 + Hundreds*100 + Thousands*1000
FROM TableNum, TableNum as Tens, TableNum as Hundreds, TableNum as Thousands
WHERE TableNum.Iota + Tens.Iota*10 + Hundreds*100 + Thousands*1000
Between [Specify Lower Limit] and [Specify Upper Limit]

Add the table again for tenThousands and again for Hundred thousands and again
for ...

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[David Mueller]

Doing the math in the SELECT! Sometimes I wonder how I get by. Thanks, Duane.

You can create a single table tblNums with a numeric field [Num] and values 0
- 9.
Then create a cartesian query with SQL like:

SELECT
[Ones].[Num]+[Tens].[Num]*10+[Hundreds].[Num]*100+[Thousands].[Num]*1000+[TenThousands].[Num]*10000 AS Expr1
FROM tblNums AS Ones, tblNums AS Tens, tblNums AS Hundreds, tblNums AS
Thousands, tblNums AS TenThousands;

This example creates numbers 0 - 99999. You can add in more copies of the
same table to up the maximum number.
--
Duane Hookom
Microsoft Access MVP

I can make this work but I'm fishing for a more elegant solution - one that
does not use a table.

A. I need to generate a range of consecutive numbers from thin air. From 0
to 99999. Example: 0, 1, 2, 3, ... 99997, 99998, 99999.

B. I'm using a table that holds values for the Ones place, Tens Place,
etc... up to Ten Thousands. 10 records for the "ones" place, 0 through 9. 10
records for the "tens" place, 0, 10, 20, ... 90. Etc. and so on for hundreds,
thousands, ...

I tried using a bunch of union queries in place of those 5 [SELECT...]
statements in the FROM clause, but I can't seem to make that work.

Thanks,
David


--- this is the SQL I'm using ---

SELECT [Ones]+[tens]+[hundreds]+[thousands]+[tenthousands] AS MyNum FROM

[select numbers as Ones from tbl_numbers where place_value = 1]. as
OnesPlace,
[select numbers as Tens from tbl_numbers where place_value = 10]. as
TensPlace ,
[select numbers as Hundreds from tbl_numbers where place_value = 100]. as
HundredsPlace,
[select numbers as Thousands from tbl_numbers where place_value = 1000]. as
ThousandsPlace,
[select numbers as TenThousands from tbl_numbers where place_value = 10000].
as TenThounsandsPlace

 

[Jeff Boyce]

David

Duane's offered a SQL/Access-based approach.

You didn't mention how you intend to use the consecutive numbers, so here's
another way to get a list ...

Open Excel.
Type 1 in the first cell.
Move down one cell.
Type 2 in the second cell.
Highlight the two cells, grab the little "ear-thingie" in the bottom right
and drag it down a bunch of rows.
Excel will automatically 'extend' the series.

Regards

Jeff Boyce
Microsoft Office/Access MVP

The OTHER Carpenter's Rule -- "Cut it long and hammer it into place"