Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2002
    Posts
    2

    Question 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. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    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
  •