Grouping Numbers

[clarencelai]

Hi..

I have a set of numbers that are not sequential. For example, 1, 2, 3, 4, 6,7,8,9,10,14,15.

I have tried to use the FIRST/LAST and MIN/MAX in QUERY but it didn't work. It will return 1 and 15.

How do I get results returned as (1,4), (6,10) and (14,15)?

VB codes or SQL would be great!

Cheers!
Clarence

[FishVal]

Hi, Clarence.

Clarify please what do you actually want to do with these "groups".

[clarencelai]

Hi FishVal,

Thanks for your reply.

Please let me elaborate.

I have two fields named LEVEL and BIN_NO (see below).

LEVEL BIN_NO
A 1
A 2
A 3
A 5
A 6
A 7
B 1
B 2
B 3
B 4
B 8
B 9
B 10

I would like the query to return results as follow.

LEVEL BIN_NO
A 1 to 3
A 5 to 7
B 1 to 4
B 8 to 10

The results will be used to inform user specifically which level/bin contained items in it.

Cheers!
Clarence

Hi, Clarence.

Clarify please what do you actually want to do with these "groups".

[FishVal]

Hi FishVal,

Thanks for your reply.

Please let me elaborate.

I have two fields named LEVEL and BIN_NO (see below).

LEVEL BIN_NO
A 1
A 2
A 3
A 5
A 6
A 7
B 1
B 2
B 3
B 4
B 8
B 9
B 10

I would like the query to return results as follow.

LEVEL BIN_NO
A 1 to 3
A 5 to 7
B 1 to 4
B 8 to 10

The results will be used to inform user specifically which level/bin contained items in it.

Cheers!
Clarence

Hi, Clarence.

I have something like this in one of my projects.
It will return you

LEVEL BIN_NO
A 1 to 3, 5 to 7
B 1 to 4, 8 to 10

Place the following function in a code module.

Public Function BinSummary(ByVal strLevel As Variant) As String
 
    Dim RS As New ADODB.Recordset
    Dim lngBinNo As Long
    Dim strSQL as String
 
    If IsNull(strLevel) Then Exit Function
 
    lngBinNo = -1
 
    strSQL="SELECT BIN_NO FROM tblYourTable WHERE LEVEL='" & _
        strLevel & "';"
 
    With RS
        .ActiveConnection = CurrentProject.Connection
        .CursorType = adOpenForwardOnly
        .LockType = adLockReadOnly
        .Open strSQL
        While Not .EOF
            If lngBinNo + 1 = ![BIN_NO] Then
                If Right(BinSummary, 1) <> " to " Then _
                    BinSummary = BinSummary & "-"
            Else
                If Right(BinSummary, 1) = " to " Then _
                    BinSummary = BinSummary & lngBinNo
                BinSummary = BinSummary & ", " & ![BIN_NO]
            End If
            lngStockPrepID = ![BIN_NO]
            .MoveNext
        Wend
        .Close
    End With
 
    If Right(BinSummary, 1) = " to " Then _
                    BinSummary = BinSummary & lngBinNo
    BinSummary = Right(BinSummary, Len(BinSummary) - 2)
 
    Set RS = Nothing
 
End Function
 
 

And run the following query.

SELECT tblYourTable.LEVEL, PrepStocksSummary(tblYourTable.LEVEL) AS txtBinSummary
FROM tblYourTable
GROUP BY tblYourTable.LEVEL;
 

[clarencelai]

Hi FishVal,

It seems like "PrepStocksSummary" was not defined.

Cheers!
Clarence

Hi, Clarence.

I have something like this in one of my projects.
It will return you

LEVEL BIN_NO
A 1 to 3, 5 to 7
B 1 to 4, 8 to 10

Place the following function in a code module.

Public Function BinSummary(ByVal strLevel As Variant) As String
 
    Dim RS As New ADODB.Recordset
    Dim lngBinNo As Long
    Dim strSQL as String
 
    If IsNull(strLevel) Then Exit Function
 
    lngBinNo = -1
 
    strSQL="SELECT BIN_NO FROM tblYourTable WHERE LEVEL='" & _
        strLevel & "';"
 
    With RS
        .ActiveConnection = CurrentProject.Connection
        .CursorType = adOpenForwardOnly
        .LockType = adLockReadOnly
        .Open strSQL
        While Not .EOF
            If lngBinNo + 1 = ![BIN_NO] Then
                If Right(BinSummary, 1) <> " to " Then _
                    BinSummary = BinSummary & "-"
            Else
                If Right(BinSummary, 1) = " to " Then _
                    BinSummary = BinSummary & lngBinNo
                BinSummary = BinSummary & ", " & ![BIN_NO]
            End If
            lngStockPrepID = ![BIN_NO]
            .MoveNext
        Wend
        .Close
    End With
 
    If Right(BinSummary, 1) = " to " Then _
                    BinSummary = BinSummary & lngBinNo
    BinSummary = Right(BinSummary, Len(BinSummary) - 2)
 
    Set RS = Nothing
 
End Function
 
 

And run the following query.

SELECT tblYourTable.LEVEL, PrepStocksSummary(tblYourTable.LEVEL) AS txtBinSummary
FROM tblYourTable
GROUP BY tblYourTable.LEVEL;
 

[FishVal]

Hi FishVal,

It seems like "PrepStocksSummary" was not defined.

Cheers!
Clarence

It seems like you just copypasted code without making an effort to understand it's logic.
I picked it from working db and replaced names with your ones. No wonder I missed to replace some (1 in function and 1 in query).

BTW the code was adapted on-fly w/o testing, I don't guarantee it will work as is.
Below is fixed variant.

Public Function BinSummary(ByVal strLevel As Variant) As String
 
    Dim RS As New ADODB.Recordset
    Dim lngBinNo As Long
    Dim strSQL as String
 
    If IsNull(strLevel) Then Exit Function
 
    lngBinNo = -1
 
    strSQL="SELECT BIN_NO FROM tblYourTable WHERE LEVEL='" & _
        strLevel & "' ORDER BY BIN_NO;"
 
    With RS
        .ActiveConnection = CurrentProject.Connection
        .CursorType = adOpenForwardOnly
        .LockType = adLockReadOnly
        .Open strSQL
        While Not .EOF
            If lngBinNo + 1 = ![BIN_NO] Then
                If Right(BinSummary, 1) <> " to " Then _
                    BinSummary = BinSummary & " to "
            Else
                If Right(BinSummary, 1) = " to " Then _
                    BinSummary = BinSummary & lngBinNo
                BinSummary = BinSummary & ", " & ![BIN_NO]
            End If
            lngBinNo = ![BIN_NO]
            .MoveNext
        Wend
        .Close
    End With
 
    If Right(BinSummary, 1) = " to " Then _
                    BinSummary = BinSummary & lngBinNo
    BinSummary = Right(BinSummary, Len(BinSummary) - 2)
 
    Set RS = Nothing
 
End Function
 
 

SELECT tblYourTable.LEVEL, BinSummary(tblYourTable.LEVEL) AS txtBinSummary
FROM tblYourTable
GROUP BY tblYourTable.LEVEL;
 

[clarencelai]

Hi FishVal,

Thanks for your email and further explanation.

I did not simply cut n paste without making changes to your code.

I must say I do not understand the code 100% though.

All the field names are replaced with the correct ones in my table for the purpose of my query.

However, I must admit that I do not understand why there was undefined "PrepStocksSummary" function there. In fact, I did replace them with BinSummary but it return some strange results as well.

Now, to isolate and confirm if your codes work, I created "tblYourTable" with LEVEL and BIN_NO as the exact field names.

The query returns the following results

A 1 to to to , 6 to to to to to
B 1 to to , 8 to to

I would appreciate if you could assist me further.

Cheers!
Clarence
PS: I am a beginner to programming and not very familiar with VB codes. I am keen to learn though. :-o)

It seems like you just copypasted code without making an effort to understand it's logic.
I picked it from working db and replaced names with your ones. No wonder I missed to replace some (1 in function and 1 in query).

BTW the code was adapted on-fly w/o testing, I don't guarantee it will work as is.
Below is fixed variant.

Public Function BinSummary(ByVal strLevel As Variant) As String
 
    Dim RS As New ADODB.Recordset
    Dim lngBinNo As Long
    Dim strSQL as String
 
    If IsNull(strLevel) Then Exit Function
 
    lngBinNo = -1
 
    strSQL="SELECT BIN_NO FROM tblYourTable WHERE LEVEL='" & _
        strLevel & "' ORDER BY BIN_NO;"
 
    With RS
        .ActiveConnection = CurrentProject.Connection
        .CursorType = adOpenForwardOnly
        .LockType = adLockReadOnly
        .Open strSQL
        While Not .EOF
            If lngBinNo + 1 = ![BIN_NO] Then
                If Right(BinSummary, 1) <> " to " Then _
                    BinSummary = BinSummary & " to "
            Else
                If Right(BinSummary, 1) = " to " Then _
                    BinSummary = BinSummary & lngBinNo
                BinSummary = BinSummary & ", " & ![BIN_NO]
            End If
            lngBinNo = ![BIN_NO]
            .MoveNext
        Wend
        .Close
    End With
 
    If Right(BinSummary, 1) = " to " Then _
                    BinSummary = BinSummary & lngBinNo
    BinSummary = Right(BinSummary, Len(BinSummary) - 2)
 
    Set RS = Nothing
 
End Function
 
 

SELECT tblYourTable.LEVEL, BinSummary(tblYourTable.LEVEL) AS txtBinSummary
FROM tblYourTable
GROUP BY tblYourTable.LEVEL;
 

[FishVal]

Hi, Clarence.

As I've said the code was changed on-fly without testing.
Below are the further fixes and some comments. Hope this will work.

Public Function BinSummary(ByVal strLevel As Variant) As String
 
    Dim RS As New ADODB.Recordset
    Dim lngBinNo As Long
    Dim strSQL as String
 
    If IsNull(strLevel) Then Exit Function
 
    lngBinNo = -1
 
    'retrieve BIN_NO related to particular LEVEL in ascending order
    strSQL="SELECT BIN_NO FROM tblYourTable WHERE LEVEL='" & _
        strLevel & "' ORDER BY BIN_NO;"
 
    With RS
        '----------- Open recordset to get records
        .ActiveConnection = CurrentProject.Connection
        .CursorType = adOpenForwardOnly
        .LockType = adLockReadOnly
        .Open strSQL
        '-------------------------------------------------
        '--------loop through retrieved records
        While Not .EOF
            '----if current BIN_NO = previous+1 then add to output " to " if not yet
            If lngBinNo + 1 = ![BIN_NO] Then
                If Right(BinSummary, 4) <> " to " Then _
                    BinSummary = BinSummary & " to "
            '---- if not so add comma and BIN_NO as is
            Else
                If Right(BinSummary, 4) = " to " Then _
                    BinSummary = BinSummary & lngBinNo
                BinSummary = BinSummary & ", " & ![BIN_NO]
            End If
            '---store current BIN_NO to compare with the next one (see above)
            lngBinNo = ![BIN_NO]
            .MoveNext
        Wend
        .Close
    End With
 
    '------- finalize output
    If Right(BinSummary, 4) = " to " Then _
                    BinSummary = BinSummary & lngBinNo
    BinSummary = Right(BinSummary, Len(BinSummary) - 2)
 
    Set RS = Nothing
 
End Function
 
 

[clarencelai]

Hi FishVal,

Thank you so much!

The code works on a fly!

Pardon me but I have one more request.

Now, if there is another level above LEVEL, how can the codes be modified to suit the need? For example, if I need to further break down by the rack number (RACK_NO), how can the code be modified to suit the need?

RACK_NO ==> LEVEL ==> BIN_NO

Rack_No LEVEL BIN_NO
AA , A, 1
AA , A, 2
AA , A, 3
BB , A, 1
BB , A, 2
BB , A, 3
BB , A, 4


Desired returned results would be..

AA, A, 1 to 3
BB, A, 1 to 4

Appreciate your advice.


Best Regards,
Clarence

Hi, Clarence.

As I've said the code was changed on-fly without testing.
Below are the further fixes and some comments. Hope this will work.

Public Function BinSummary(ByVal strLevel As Variant) As String
 
    Dim RS As New ADODB.Recordset
    Dim lngBinNo As Long
    Dim strSQL as String
 
    If IsNull(strLevel) Then Exit Function
 
    lngBinNo = -1
 
    'retrieve BIN_NO related to particular LEVEL in ascending order
    strSQL="SELECT BIN_NO FROM tblYourTable WHERE LEVEL='" & _
        strLevel & "' ORDER BY BIN_NO;"
 
    With RS
        '----------- Open recordset to get records
        .ActiveConnection = CurrentProject.Connection
        .CursorType = adOpenForwardOnly
        .LockType = adLockReadOnly
        .Open strSQL
        '-------------------------------------------------
        '--------loop through retrieved records
        While Not .EOF
            '----if current BIN_NO = previous+1 then add to output " to " if not yet
            If lngBinNo + 1 = ![BIN_NO] Then
                If Right(BinSummary, 4) <> " to " Then _
                    BinSummary = BinSummary & " to "
            '---- if not so add comma and BIN_NO as is
            Else
                If Right(BinSummary, 4) = " to " Then _
                    BinSummary = BinSummary & lngBinNo
                BinSummary = BinSummary & ", " & ![BIN_NO]
            End If
            '---store current BIN_NO to compare with the next one (see above)
            lngBinNo = ![BIN_NO]
            .MoveNext
        Wend
        .Close
    End With
 
    '------- finalize output
    If Right(BinSummary, 4) = " to " Then _
                    BinSummary = BinSummary & lngBinNo
    BinSummary = Right(BinSummary, Len(BinSummary) - 2)
 
    Set RS = Nothing
 
End Function
 
 

[FishVal]

Hi FishVal,

Thank you so much!

The code works on a fly!

Pardon me but I have one more request.

Now, if there is another level above LEVEL, how can the codes be modified to suit the need? For example, if I need to further break down by the rack number (RACK_NO), how can the code be modified to suit the need?

RACK_NO ==> LEVEL ==> BIN_NO

Rack_No LEVEL BIN_NO
AA , A, 1
AA , A, 2
AA , A, 3
BB , A, 1
BB , A, 2
BB , A, 3
BB , A, 4


Desired returned results would be..

AA, A, 1 to 3
BB, A, 1 to 4

Appreciate your advice.


Best Regards,
Clarence

Hi, Clarence.

Glad you've got it working.
Concerning your further question I would like to say - "exactly the same way but using two fields in the query grouping and filter criteria in BinSummary".

Smthng like this:

SELECT tblYourTable.RACK_NO, tblYourTable.LEVEL, BinSummary(tblYourTable.RACK_NO, tblYourTable.LEVEL) AS txtBinSummary
FROM tblYourTable
GROUP BY tblYourTable.RACK_NO, tblYourTable.LEVEL;
 

Public Function BinSummary(ByVal strRackNo as Variant, _
                                           ByVal strLevel As Variant) As String
 
    Dim RS As New ADODB.Recordset
    Dim lngBinNo As Long
    Dim strSQL as String
 
    If IsNull(strLevel) Or IsNull(strRackNo) Then Exit Function
 
    lngBinNo = -1
 
    'retrieve BIN_NO related to particular LEVEL in ascending order
    strSQL="SELECT BIN_NO FROM tblYourTable WHERE LEVEL='" & _
        strLevel & "' AND RACK_NO='" & strRackNo & "' ORDER BY BIN_NO;"
 
    With RS
        '----------- Open recordset to get records
        .ActiveConnection = CurrentProject.Connection
        .CursorType = adOpenForwardOnly
        .LockType = adLockReadOnly
        .Open strSQL
        '-------------------------------------------------
        '--------loop through retrieved records
        While Not .EOF
            '----if current BIN_NO = previous+1 then add to output " to " if not yet
            If lngBinNo + 1 = ![BIN_NO] Then
                If Right(BinSummary, 4) <> " to " Then _
                    BinSummary = BinSummary & " to "
            '---- if not so add comma and BIN_NO as is
            Else
                If Right(BinSummary, 4) = " to " Then _
                    BinSummary = BinSummary & lngBinNo
                BinSummary = BinSummary & ", " & ![BIN_NO]
            End If
            '---store current BIN_NO to compare with the next one (see above)
            lngBinNo = ![BIN_NO]
            .MoveNext
        Wend
        .Close
    End With
 
    '------- finalize output
    If Right(BinSummary, 4) = " to " Then _
                    BinSummary = BinSummary & lngBinNo
    BinSummary = Right(BinSummary, Len(BinSummary) - 2)
 
    Set RS = Nothing
 
End Function
 
 

[clarencelai]

Hi Fish Val,

It works! It works!

Thank you so much for your help!

Cheers!
Clarence

Hi, Clarence.

Glad you've got it working.
Concerning your further question I would like to say - "exactly the same way but using two fields in the query grouping and filter criteria in BinSummary".

Smthng like this:

SELECT tblYourTable.RACK_NO, tblYourTable.LEVEL, BinSummary(tblYourTable.RACK_NO, tblYourTable.LEVEL) AS txtBinSummary
FROM tblYourTable
GROUP BY tblYourTable.RACK_NO, tblYourTable.LEVEL;
 

Public Function BinSummary(ByVal strRackNo as Variant, _
                                           ByVal strLevel As Variant) As String
 
    Dim RS As New ADODB.Recordset
    Dim lngBinNo As Long
    Dim strSQL as String
 
    If IsNull(strLevel) Or IsNull(strRackNo) Then Exit Function
 
    lngBinNo = -1
 
    'retrieve BIN_NO related to particular LEVEL in ascending order
    strSQL="SELECT BIN_NO FROM tblYourTable WHERE LEVEL='" & _
        strLevel & "' AND RACK_NO='" & strRackNo & "' ORDER BY BIN_NO;"
 
    With RS
        '----------- Open recordset to get records
        .ActiveConnection = CurrentProject.Connection
        .CursorType = adOpenForwardOnly
        .LockType = adLockReadOnly
        .Open strSQL
        '-------------------------------------------------
        '--------loop through retrieved records
        While Not .EOF
            '----if current BIN_NO = previous+1 then add to output " to " if not yet
            If lngBinNo + 1 = ![BIN_NO] Then
                If Right(BinSummary, 4) <> " to " Then _
                    BinSummary = BinSummary & " to "
            '---- if not so add comma and BIN_NO as is
            Else
                If Right(BinSummary, 4) = " to " Then _
                    BinSummary = BinSummary & lngBinNo
                BinSummary = BinSummary & ", " & ![BIN_NO]
            End If
            '---store current BIN_NO to compare with the next one (see above)
            lngBinNo = ![BIN_NO]
            .MoveNext
        Wend
        .Close
    End With
 
    '------- finalize output
    If Right(BinSummary, 4) = " to " Then _
                    BinSummary = BinSummary & lngBinNo
    BinSummary = Right(BinSummary, Len(BinSummary) - 2)
 
    Set RS = Nothing
 
End Function
 
 

[FishVal]

Hi Fish Val,

It works! It works!

Thank you so much for your help!

Cheers!
Clarence

Not a problem. You are welcome.

Best regards,
Fish