×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Creating Ranges from List of Numbers

Creating Ranges from List of Numbers

Creating Ranges from List of Numbers

(OP)
Hi There, I have what I hope is an easy problem I'd like to solve using VBA.  I have a 2-field table, like:

Field1  Field2
A       001
A       002
A       003
B       004
B       005
A       006
A       007
A       008

That I'd like to convert to:

Field1   Start   End
A        001     003
B        004     005   
A        006     008

Essentially, creating ranges out of a list of values in field2 for each consecutive instance of field1.

Any help would be greatly appreciated!!  

RE: Creating Ranges from List of Numbers

How about

SELECT Field1, Format(MIN(Field2),'000') As Start, Format(MAX(Field2),'000') as End
FROM YourTable
group by Field1

 

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: Creating Ranges from List of Numbers

oops, just realized this will NOT work!!!

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: Creating Ranges from List of Numbers

You are really missing another column in your table.  A table is an UNORDERED relation.  You do not have enought data in your table to adequately define your result.

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: Creating Ranges from List of Numbers

It may be possible. This occurs to me, but needs more work:

SELECT tblT.Field1, tblT.Field2, getmax([Field1]) AS MaxOfField2
FROM tblT


CODE

Dim LastMax As String

Function GetMax(fIn)
Dim rs As DAO.Recordset

strSQL = "Select Field1, Field2 From tblT Where Field2>'" & LastMax & "'"
Set rs = CurrentDb.OpenRecordset(strSQL)

Do While True
    If rs.EOF And rs.BOF Then
        GetMax = DMax("Field2", "tblT")
        Exit Do
    End If
    If rs.EOF Then
        rs.MovePrevious
        GetMax = rs!Field2
        LastMax = GetMax
        Exit Do
    End If
    If rs!Field1 = fIn Then
        rs.MoveNext
    Else
        rs.MovePrevious
        If rs.BOF Then
            GetMax = LastMax
        Else
            GetMax = rs!Field2
            LastMax = GetMax
        End If
        Exit Do
    End If
Loop

End Function

http://lessthandot.com

RE: Creating Ranges from List of Numbers

(OP)
Thanks Remou!  I think this gets me on the right track clown

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close