Results 1 to 8 of 8
  1. #1
    rlmarkwell is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jul 2016
    Posts
    5

    Separate query results based on range between two columns

    I have a large table that has two columns with a range beginning numbers and ending numbers. I am wanting to build a query that will spit out individual results into a single column, but am at a block in figuring it out. Here is an example:
    first last
    1234 1236
    5678 5681
    The query would show:
    1234
    1235
    1236
    5678
    5679
    5680
    5681

    Attached Thumbnails Attached Thumbnails tst.png  

  2. #2
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Create a table to hold the values and calculate them in code. Then base your output on that table. Maybe something like this:

    rs1 = Select * From tblValues
    rs2 = tblNewTable
    Do Until rs1.EOF
    For x = rs1!FirstValue to rs1!LastValue
    rs2.AddNew
    rs2!Value = x
    rs2.Update
    Next x
    rs1.MoveNext
    Loop

  3. #3
    rlmarkwell is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jul 2016
    Posts
    5
    Thank you! That steered me in the right direction, ended up with this:
    Private Sub Command0_Click()
    Set db = CurrentDb
    Set rs1 = db.OpenRecordset("Select * From TEST", dbOpenDynaset, dbSeeChanges)
    Set rs2 = db.OpenRecordset("RESULTS")
    Do Until rs1.EOF
    For x = rs1!First To rs1!Last
    rs2.AddNew
    rs2!DID = x
    rs2.Update
    Next x
    rs1.MoveNext
    Loop
    End Sub

  4. #4
    rlmarkwell is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jul 2016
    Posts
    5
    I found it might be beneficial to add an index number to group that was expanding from range to single entries into rs2 to link this wto other tables. Is there an easy method to accomplish this by modifying the code from previous?

    Click image for larger version. 

Name:	sample.png 
Views:	7 
Size:	8.2 KB 
ID:	25302

  5. #5
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Not sure I follow you, do you want to add the seqID to the Results table? If so I think just adding line below would do it.

    Do Until rs1.EOF
    For x = rs1!First To rs1!Last
    rs2.AddNew
    rs2!DID = x
    rs2!SeqID = rs1!SeqID
    rs2.Update
    Next x
    rs1.MoveNext
    Loop

  6. #6
    rlmarkwell is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jul 2016
    Posts
    5
    Sorry, I wasn't clear. Lets say the original record set had an index number (or sequence number) associated with each line of data. I am wanting to carry that over to the second table along with the converted range to single entries.

    So, if the first line of data in record set 1 has:
    Seq First Last
    1 1234 1236
    2 5678 5681
    3 7890 7891

    The desired result in record set 2 would be:
    1 1234
    1 1235
    1 1236
    2 5678
    2 5679
    2 5680
    2 5681
    3 7890
    3 7891

  7. #7
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    That 2nd code I posted should work.

  8. #8
    rlmarkwell is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jul 2016
    Posts
    5
    That's exactly what I needed! Thank you!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 8
    Last Post: 06-05-2015, 02:17 PM
  2. Replies: 2
    Last Post: 06-26-2014, 07:42 AM
  3. Can't separate project data based on date range
    By jason.cyr13 in forum Queries
    Replies: 1
    Last Post: 01-29-2014, 11:32 AM
  4. Replies: 3
    Last Post: 12-14-2013, 06:16 PM
  5. Replies: 1
    Last Post: 03-12-2013, 01:20 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums