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,1 5.

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.
[code=vb]
Public Function BinSummary(ByVa l 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
.ActiveConnecti on = CurrentProject. Connection
.CursorType = adOpenForwardOn ly
.LockType = adLockReadOnly
.Open strSQL
While Not .EOF
If lngBinNo + 1 = ![BIN_NO] Then
If Right(BinSummar y, 1) <> " to " Then _
BinSummary = BinSummary & "-"
Else
If Right(BinSummar y, 1) = " to " Then _
BinSummary = BinSummary & lngBinNo
BinSummary = BinSummary & ", " & ![BIN_NO]
End If
lngStockPrepID = ![BIN_NO]
.MoveNext
Wend
.Close
End With

If Right(BinSummar y, 1) = " to " Then _
BinSummary = BinSummary & lngBinNo
BinSummary = Right(BinSummar y, Len(BinSummary) - 2)

Set RS = Nothing

End Function

[/code]

And run the following query.

[code=sql]
SELECT tblYourTable.LE VEL, PrepStocksSumma ry(tblYourTable .LEVEL) AS txtBinSummary
FROM tblYourTable
GROUP BY tblYourTable.LE VEL;
[/code]

[clarencelai]

Hi FishVal,

It seems like "PrepStocksSumm ary" 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.
[code=vb]
Public Function BinSummary(ByVa l 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
.ActiveConnecti on = CurrentProject. Connection
.CursorType = adOpenForwardOn ly
.LockType = adLockReadOnly
.Open strSQL
While Not .EOF
If lngBinNo + 1 = ![BIN_NO] Then
If Right(BinSummar y, 1) <> " to " Then _
BinSummary = BinSummary & "-"
Else
If Right(BinSummar y, 1) = " to " Then _
BinSummary = BinSummary & lngBinNo
BinSummary = BinSummary & ", " & ![BIN_NO]
End If
lngStockPrepID = ![BIN_NO]
.MoveNext
Wend
.Close
End With

If Right(BinSummar y, 1) = " to " Then _
BinSummary = BinSummary & lngBinNo
BinSummary = Right(BinSummar y, Len(BinSummary) - 2)

Set RS = Nothing

End Function

[/code]

And run the following query.

[code=sql]
SELECT tblYourTable.LE VEL, PrepStocksSumma ry(tblYourTable .LEVEL) AS txtBinSummary
FROM tblYourTable
GROUP BY tblYourTable.LE VEL;
[/code]

[FishVal]

Hi FishVal,

It seems like "PrepStocksSumm ary" 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.

[code=vb]
Public Function BinSummary(ByVa l 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
.ActiveConnecti on = CurrentProject. Connection
.CursorType = adOpenForwardOn ly
.LockType = adLockReadOnly
.Open strSQL
While Not .EOF
If lngBinNo + 1 = ![BIN_NO] Then
If Right(BinSummar y, 1) <> " to " Then _
BinSummary = BinSummary & " to "
Else
If Right(BinSummar y, 1) = " to " Then _
BinSummary = BinSummary & lngBinNo
BinSummary = BinSummary & ", " & ![BIN_NO]
End If
lngBinNo = ![BIN_NO]
.MoveNext
Wend
.Close
End With

If Right(BinSummar y, 1) = " to " Then _
BinSummary = BinSummary & lngBinNo
BinSummary = Right(BinSummar y, Len(BinSummary) - 2)

Set RS = Nothing

End Function

[/code]


[code=sql]
SELECT tblYourTable.LE VEL, BinSummary(tblY ourTable.LEVEL) AS txtBinSummary
FROM tblYourTable
GROUP BY tblYourTable.LE VEL;
[/code]

[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 "PrepStocksSumm ary" 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 "tblYourTab le" 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.

[code=vb]
Public Function BinSummary(ByVa l 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
.ActiveConnecti on = CurrentProject. Connection
.CursorType = adOpenForwardOn ly
.LockType = adLockReadOnly
.Open strSQL
While Not .EOF
If lngBinNo + 1 = ![BIN_NO] Then
If Right(BinSummar y, 1) <> " to " Then _
BinSummary = BinSummary & " to "
Else
If Right(BinSummar y, 1) = " to " Then _
BinSummary = BinSummary & lngBinNo
BinSummary = BinSummary & ", " & ![BIN_NO]
End If
lngBinNo = ![BIN_NO]
.MoveNext
Wend
.Close
End With

If Right(BinSummar y, 1) = " to " Then _
BinSummary = BinSummary & lngBinNo
BinSummary = Right(BinSummar y, Len(BinSummary) - 2)

Set RS = Nothing

End Function

[/code]


[code=sql]
SELECT tblYourTable.LE VEL, BinSummary(tblY ourTable.LEVEL) AS txtBinSummary
FROM tblYourTable
GROUP BY tblYourTable.LE VEL;
[/code]

[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.
[code=vb]
Public Function BinSummary(ByVa l 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
.ActiveConnecti on = CurrentProject. Connection
.CursorType = adOpenForwardOn ly
.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(BinSummar y, 4) <> " to " Then _
BinSummary = BinSummary & " to "
'---- if not so add comma and BIN_NO as is
Else
If Right(BinSummar y, 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(BinSummar y, 4) = " to " Then _
BinSummary = BinSummary & lngBinNo
BinSummary = Right(BinSummar y, Len(BinSummary) - 2)

Set RS = Nothing

End Function

[/code]

[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.
[code=vb]
Public Function BinSummary(ByVa l 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
.ActiveConnecti on = CurrentProject. Connection
.CursorType = adOpenForwardOn ly
.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(BinSummar y, 4) <> " to " Then _
BinSummary = BinSummary & " to "
'---- if not so add comma and BIN_NO as is
Else
If Right(BinSummar y, 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(BinSummar y, 4) = " to " Then _
BinSummary = BinSummary & lngBinNo
BinSummary = Right(BinSummar y, Len(BinSummary) - 2)

Set RS = Nothing

End Function

[/code]

[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:

[code=sql]
SELECT tblYourTable.RA CK_NO, tblYourTable.LE VEL, BinSummary(tblY ourTable.RACK_N O, tblYourTable.LE VEL) AS txtBinSummary
FROM tblYourTable
GROUP BY tblYourTable.RA CK_NO, tblYourTable.LE VEL;
[/code]

[code=vb]
Public Function BinSummary(ByVa l 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(strRackN o) 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
.ActiveConnecti on = CurrentProject. Connection
.CursorType = adOpenForwardOn ly
.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(BinSummar y, 4) <> " to " Then _
BinSummary = BinSummary & " to "
'---- if not so add comma and BIN_NO as is
Else
If Right(BinSummar y, 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(BinSummar y, 4) = " to " Then _
BinSummary = BinSummary & lngBinNo
BinSummary = Right(BinSummar y, Len(BinSummary) - 2)

Set RS = Nothing

End Function

[/code]

[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:

[code=sql]
SELECT tblYourTable.RA CK_NO, tblYourTable.LE VEL, BinSummary(tblY ourTable.RACK_N O, tblYourTable.LE VEL) AS txtBinSummary
FROM tblYourTable
GROUP BY tblYourTable.RA CK_NO, tblYourTable.LE VEL;
[/code]

[code=vb]
Public Function BinSummary(ByVa l 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(strRackN o) 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
.ActiveConnecti on = CurrentProject. Connection
.CursorType = adOpenForwardOn ly
.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(BinSummar y, 4) <> " to " Then _
BinSummary = BinSummary & " to "
'---- if not so add comma and BIN_NO as is
Else
If Right(BinSummar y, 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(BinSummar y, 4) = " to " Then _
BinSummary = BinSummary & lngBinNo
BinSummary = Right(BinSummar y, Len(BinSummary) - 2)

Set RS = Nothing

End Function

[/code]

[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