Thread: Expanding a series of numbers

1. Registered User
Join Date
Jul 2002
Posts
2

Unanswered: Expanding a series of numbers

I have 1 table with 2 fields:
Field 1 is Start Number and Field 2 is End Number. Is there a way that I can create a query which would expand the series to list all the numbers individually between the Start and End Number series? In other words if I have a series 1 - 10 I would like to be able to create a list of all the numbers within that series: 1, 2, 3, 4 ect to 10.

2. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
in some databases you can run SELECT 1 UNION SELECT 2 without a FROM clause and get a result set (this technique can be used to build up integers from thin air, as it were), but not in Access, so you will need a simple integers table

create table integers (i integer)
insert into integers (i) values (0)
insert into integers (i) values (1)
etc.
insert into integers (i) values (9)

now you have a table that, with a few cross joins, can give you whatever range of integers you want

for example,
Code:
```select 100*hundreds.i + 10*tens.i + ones.i + 1
from integers hundreds
, integers tens
, integers ones```
will give the integers from 1 to 1000

now all you have to do is cross join with your table and bob's your uncle
Code:
```select 100*hundreds.i + 10*tens.i + ones.i + 1
from integers hundreds
, integers tens
, integers ones
, yourtable
where 100*hundreds.i + 10*tens.i + ones.i + 1
between yourtable.StartNumber
and yourtable.EndNumber```
rudy
http://rudy.ca/

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•