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
11 1640 FishVal 2,653
Recognized Expert Specialist
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".
FishVal 2,653
Recognized Expert Specialist
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 "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. -
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 2,653
Recognized Expert Specialist
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. -
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 "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. -
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 2,653
Recognized Expert Specialist
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
-
-
FishVal 2,653
Recognized Expert Specialist
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
-
-
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 change the grouping of a single report, based on the
user's selection?
Or do I have to create another report, with different grouping, and call it
when...
|
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 multiple nodes?" I will use an example to try to explain what
I need to do and what I have for data. The example might not be
very realistic but it's...
|
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 reports at the plan (overall totals), department and division
levels which have sorting and grouping implemented with this new
|
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 friday from the first date get the sum of bookings.
|
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 that creates the 2
teams such that the total score for each team is as close as possible.
Any ideas??
Thanks!
| |
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 classes are obviously related, then I use the same namespace.
Problem is that this doesn't seem to happen often. I could understand
Balena's...
|
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 simply 1, 5, 9,10,11,16,17,25,29 and I pass in a
distance of 2 then I would want to end up with groups like
1, 5, (9,10,11), (16,17) ,25,29
So ...
|
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 more information.
'de_AT.utf8'
{'mon_decimal_point': ',', 'int_frac_digits': 2, 'p_sep_by_space': 1,
'frac_digits': 2, 'thousands_sep': '',...
|
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 on a table tblMain319. The report groups employees by their work location (). By putting a Count(*) function in the footer for the Payroll Distribution...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it. ...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 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 a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert...
| |
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |