NeoPa 32,557
Recognized Expert Moderator MVP
A number of posters have asked to be shown how to produce a list of items from multiple records which are (potentially) grouped together.
Take the following data for instance (from a table called [tblForum]) : - Zone Forum
-
Community Introductions
-
Community Community Cafe
-
Community Software Development
-
Community Jobs / Contract Work
-
Community Experts Panel
-
Programming C++ / C
-
Programming Java
-
Programming .NET
-
Programming Visual Basic
-
Programming Python
-
Web Development PHP
-
Web Development Ruby / Rails
-
Web Development Perl
-
Web Development ASP
-
Web Development ColdFusion
-
Web Development Javascript / Ajax
-
Web Development Flash
-
Web Development XML
-
Web Development WAP / WML
-
Web Development HTML / CSS
-
Database Help MySQL
-
Database Help Oracle
-
Database Help SQL Server
-
Database Help PostgreSQL
-
Database Help Access
-
Database Help DB2
-
Sys Admin & OS Apache
-
Sys Admin & OS IIS
-
Sys Admin & OS Networking
-
Sys Admin & OS Linux / Unix / BSD
-
Sys Admin & OS Windows
If you wanted this to be grouped together into lists (as below) then read on. - Zone Forums
-
Community Introductions, Community Cafe, Software Development, Jobs / Contract Work, Experts Panel
-
Programming C++ / C, Java, .NET, Visual Basic, Python
-
Web Development PHP, Ruby / Rails, Perl, ASP, ColdFusion, Javascript / Ajax, Flash, XML, WAP / WML, HTML / CSS
-
Database Help MySQL, Oracle, SQL Server, PostgreSQL, Access, DB2
-
Sys Admin & OS Apache, IIS, Networking, Linux / Unix / BSD, Windows
Firstly, create a function in a standard module similar to the example code attached : - 'Concat Returns lists of items which are within a grouped field
-
Public Function Concat(strGroup As String, _
-
strItem As String) As String
-
Static strLastGroup As String
-
Static strItems As String
-
-
If strGroup = strLastGroup Then
-
strItems = strItems & ", " & strItem
-
Else
-
strLastGroup = strGroup
-
strItems = strItem
-
End If
-
Concat = strItems
-
End Function
The SQL for calling this function would be of the form : - SELECT [Zone],
-
Max(Concat([Zone], [Forum])) AS [Forums]
-
FROM [tblForum]
-
GROUP BY [Zone]
Another form of this (to drop duplicates) would be : - 'Concat Returns lists of items which are within a grouped field
-
Public Function Concat(strGroup As String, _
-
strItem As String) As String
-
Static strLastGroup As String
-
Static strItems As String
-
-
If strGroup = strLastGroup Then
-
If InStr(", " & strItems & ", ", ", " & strItem & ", ") = 0 Then _
-
strItems = strItems & ", " & strItem
-
Else
-
strLastGroup = strGroup
-
strItems = strItem
-
End If
-
Concat = strItems
-
End Function
1 8198
This is nice.
I combined the two functions by adding a third parameter "uniq" -
'Concat Returns lists of items which are within a grouped field
-
'Set uniq=-1 (True) to return only unique values.
-
Public Function ConcatLine(strGroup As String, _
-
strItem As String, uniq As Boolean) As String
-
Static strLastGroup As String
-
Static strItems As String
-
Static i As Integer
-
-
If strGroup = strLastGroup Then
-
If uniq Then
-
If InStr(", " & strItems & ", ", ", " & strItem & ", ") = 0 Then
-
strItems = strItems & ", " & strItem
-
End If
-
Else
-
strItems = strItems & ", " & strItem
-
End If
-
Else
-
strLastGroup = strGroup
-
strItems = strItem
-
End If
-
ConcatLine = strItems
-
i = i + 1
-
Debug.Print i & " "; Len(ConcatLine) & " " & ConcatLine
-
End Function
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: arthur-e |
last post by:
I can filter a query for a report on a field of a subform BUT now....
I'd like to be able to select more than one item in a multi-list box
to select all the records.
ALSO
to use two fields (or...
|
by: Brandon Schultz |
last post by:
I have created a list box so that I can navigate through records. The
AfterUpdate event looks like this;
Me.RecordsetClone.FindFirst " = '" & Me! &
"'"
Me.Bookmark = Me.RecordsetClone.Bookmark
...
|
by: Jed Parsons |
last post by:
Hi,
I'm using the logging module for the first time. I'm using it from
within Zope Extensions.
My problem is that, for every event logged, the logger is producing
multiple identical entries...
|
by: beginner |
last post by:
Hi Everyone,
I have a simple list reconstruction problem, but I don't really know
how to do it.
I have a list that looks like this:
l=
What I want to do is to reorganize it in groups,...
|
by: Grubsy4u |
last post by:
Hi all,
I have recently created a list box in a form.....I wanted to know if there was a way of searching for multiple records within the list box.
How can i get this list box to search for...
| |
by: Joel Koltner |
last post by:
Is there an easy way to get a list comprehension to produce a flat list of,
say, for each input argument?
E.g., I'd like to do something like:
for x in range(4) ]
....and receive
|
by: KrazyKasper |
last post by:
Access 2003 – Multi-Column List Box – Select Multiple Items
I have a multi-column (3 columns) list box that works well to select one set of records or all sets of records (based on the first field...
|
by: woodey2002 |
last post by:
Hi Everyone and many thanks for your time.. I am trying to begin access and a bit of VBA i am enjoying it but I have a annoying problem I just can’t get any where on.
My databse mostly includes...
|
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,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
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,...
| |
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...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
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...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence...
| |