How do I truncate & consolidate a list of numbers? 1

[LimitedTech]

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

 

[SkipVought]

HI,

Is this list in a table?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[MajP]

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.

 

[LimitedTech]

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.

 

[SkipVought]

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...
[pre]
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
[/pre]

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

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[LimitedTech]

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.

 

[SkipVought]

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[dhookom]

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

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
[COLOR=#4E9A06]    ' substitute your own SQL statement below[/color]
    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

 

[LimitedTech]

Thanks! I will give this a shot.