Results 1 to 2 of 2

Thread: Query Help

  1. #1
    Join Date
    Jul 2004
    Location
    South Dakota
    Posts
    267

    Unanswered: Query Help

    To All--

    I have a table where there are integers stored by low number and high number (a range in other words). For instance:

    Field1, LowNum, HighNum
    Red, 1, 3
    Blue, 4, 6

    I would like a query to return a row for each number between LowNum and HighNum. Like this.

    Field1, Number
    Red, 1
    Red, 2
    Red, 3
    Blue, 4
    Blue, 5
    Blue, 6

    Anyone have any good tricks for this one? Thanks.

    C

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. Create (only once) a tally table, i.e. a table with one column and that contains a sequence of numbers:
    Code:
    Sub RunOnce()
    
        Const C_SQLTable As String = "CREATE TABLE Tbl_Tally ( [Number] INTEGER NOT NULL CONSTRAINT pk_Tbl_Tally PRIMARY KEY);"
        Const C_SQLQuery As String = "SELECT Tbl_Tally.Number " & _
                                     "FROM Tbl_Tally LEFT JOIN Tbl_Samples ON Tbl_Tally.Number = Tbl_Samples.ID " & _
                                     "WHERE Tbl_Samples.ID Is Null;"
        
        Dim qdf As DAO.QueryDef
        Dim i As Long
        
        CurrentDb.Execute C_SQLTable, dbFailOnError
        For i = 1 To 999
            CurrentDb.Execute Replace("INSERT INTO Tbl_Tally ( [Number] ) VALUES ( @V );", "@V", i), dbFailOnError
        Next i
        
    End Sub
    2. Use this query (with MyTable being the name of your table):
    Code:
    SELECT MyTable.Field1, Tbl_Tally.Number
      FROM MyTable, Tbl_Tally
     WHERE Tbl_Tally.Number BETWEEN MyTable.LowNum AND MyTable.HighNum
    Have a nice day!

Posting Permissions

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