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!

*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.

Jobs

How do I truncate & consolidate a list of numbers?

How do I truncate & consolidate a list of numbers?

(OP)
If I start with a list of number such as this;
503422932
503422937
503422938
503422939
503422942
503422967

503423013
503423014

I would like to have the output look like this
503422932,37,38,39,42,67 503423013,14

RE: How do I truncate & consolidate a list of numbers?

HI,

Is this list in a table?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: How do I truncate & consolidate a list of numbers?

Does the output really need to be horizontal string? Could you do a form or report that shows this instead in a grouped by format?

5034229_
32
37
38
39
42
67

5034230_
13
14

The reason I ask is that a long single string would require code to loop a recordset and find where the records are the same except for the last two digits then build your string. Pretty easy if you are comfortable in vba and working in DAO or ADO. The approach I suggest could be done using pure sql in a report and using "sorting and grouping"
"Select left(MyNumberField,7) & "_" as MyNumberHeader, right(MynumberField,2) as MyNumberTail Order by MyNumberField"

Build a report and group on MyNumberHeader and put the "Tails" in the detail section.

If the tail is always 2 and the header is always 7 this would work. If the Header is different length but the tails are always 2 the above could be modified and still work. If the header and the tails could be variable lengths then you will have to write some pretty complicated code. You would have to start with the first record and then read the next record. Compare digit by digit until you found the header length. Save that as your header. Then start looping using that header and concatenating your string. Then see if the header changes. If so find the new header by comparing that record to its next record, and continue the process.

RE: How do I truncate & consolidate a list of numbers?

(OP)
The list will be in a table, imported from excel. I am using Access 2003 and it does need to be in the horizontal format.

RE: How do I truncate & consolidate a list of numbers?

"imported from excel"

So why not just do it in Excel, rather than create an extra step?

So when you say, "I would like to have the output look like this", where is this output going? To the screen, printer, somewhere else?

Here's what I've done in Excel with FORMULAS...

NumList										
503422932  503422932,	503422932,37,38,39,42,67 503423013,14 
503422937  37,									
503422938  38,									
503422939  39,									
503422942  42,									
503422967  67 									
503423013  503423013,									
503423014  14 									
 

You'd probably need some VBA to make it simpler.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: How do I truncate & consolidate a list of numbers?

(OP)
I am using Access to aggregate data from several sources, reformat them and one of the things I have to do with it is output it to Excel file.

RE: How do I truncate & consolidate a list of numbers?

What you specified can ALL be accomplished in Excel. Excel can query all your Access tables.

Or you can maintain your current process and simply query the list/table you generated in Access right into your workbook, where you eventually want the result, and do the transform there via formulas.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: How do I truncate & consolidate a list of numbers?

If you wanted to do this in Access VBA, try a function like:

CODE --> vba

Public Function ListNums()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSQL As String
    Dim strOut As String
    Dim strLeft7 As String
    Dim strFieldValue As String
    ' substitute your own SQL statement below
    strSQL = "SELECT CStr([NumberFieldName]) AS Num FROM tblLimitedTech ORDER BY CStr([NumberFieldName]);"
    Set db = CurrentDb
    Set rs = db.OpenRecordset(strSQL)
    With rs
        Do Until .EOF
            strFieldValue = .Fields(0)
            If Left(strFieldValue, 7) <> strLeft7 Then
                If Len(strOut) > 1 Then
                    strOut = Left(strOut, Len(strOut) - 1)
                End If
                strOut = strOut & " " & strFieldValue & ","
                strLeft7 = Left(strFieldValue, 7)
             Else
                strOut = strOut & Right(strFieldValue, 2) & ","
            End If
            .MoveNext
        Loop
        .Close
    End With
    strOut = Left(strOut, Len(strOut) - 1)
    ListNums = Trim(strOut)
    Set rs = Nothing
    Set db = Nothing
    
End Function 

Duane
Hook'D on Access
MS Access MVP

RE: How do I truncate & consolidate a list of numbers?

(OP)
Thanks! I will give this a shot.

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!

Resources

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