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
11 1636
Hi, Clarence.
Clarify please what do you actually want to do with these "groups".
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".
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;
-
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;
-
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;
-
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;
-
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
-
-
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
-
-
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
-
-
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
-
-
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
Sign in to post your reply or Sign up for a free account.
Similar topics
by: amber |
last post by:
Hello,
I have a report in VB.NET/Crystal Reports.
I have a criteria form that users select between 2 different types of
grouping (group by category or group by year).
Can I programmatically...
|
by: Andreas Håkansson |
last post by:
Seeing how my previous post seem to have fallen between the
cracks, I thought I would have a second, more direct, go at it.
So my question is "Is it possible to group (Muenchian method)
over...
|
by: ahaque38 |
last post by:
Hello.
Using A2K SP3, I am having the following problem with a report using
"Sorting and Grouping".
I have recently added a grouping in the reports for
"Category2<>'CONTRACTS'".
I have...
|
by: Peter Bailey |
last post by:
I have a query that returns , and :
12/05/04 3 Wednesday
13/05/04 0 Thursday
and so on
what I would like to do now is count the number of bookings by week so from
monday to...
|
by: a |
last post by:
We are writing an app that assigns people to teams based on their curent
score. Teams are 8 people, there are 2 teams. (i would like it to be
flexible, but this is a start). I need an algorithm...
|
by: _DD |
last post by:
I believe Balena's Best Practices book suggests grouping quite a few
classes into each namespace. I don't remember a number, but this has
me curious about how other programmers handle this.
If...
|
by: joh12005 |
last post by:
hello,
i'm looking for a way to have a list of number grouped by consecutive
interval, after a search, for example :
=>
, , , ]
|
by: Erik Nodland |
last post by:
Hi Everyone,
Just after some ideas and suggestions on how to do this. I have a large
amount of numeric data which I would like to group given a distance as a
parameter.
IE. If my dataset was...
|
by: Roman Bertle |
last post by:
Hello,
I try to format monetary values using the locale module, python2.5:
Python 2.5.2a0 (r251:54863, Jan 3 2008, 17:59:56)
on linux2
Type "help", "copyright", "credits" or "license" for...
|
by: patjones |
last post by:
Good afternoon:
This seems like it shouldn't be hard, and then again this is how so many problems seem at the outset. My situation is this:
I have a report called rptMain319, which is based...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
| |