Connecting Tech Pros Worldwide Help | Site Map
bytes > topic > access > insights

Producing a List from Multiple Records

  #1  
Old May 1st, 2007, 12:05 AM
NeoPa's Avatar
Administrator
  
Join Date: Oct 2006
Location: London - UK
Posts: 15,461
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]) :
Expand|Select|Wrap|Line Numbers
  1. Zone             Forum
  2. Community        Introductions
  3. Community        Community Cafe
  4. Community        Software Development
  5. Community        Jobs / Contract Work
  6. Community        Experts Panel
  7. Programming      C++ / C
  8. Programming      Java
  9. Programming      .NET
  10. Programming      Visual Basic
  11. Programming      Python
  12. Web Development  PHP
  13. Web Development  Ruby / Rails
  14. Web Development  Perl
  15. Web Development  ASP
  16. Web Development  ColdFusion
  17. Web Development  Javascript / Ajax
  18. Web Development  Flash
  19. Web Development  XML
  20. Web Development  WAP / WML
  21. Web Development  HTML / CSS
  22. Database Help    MySQL
  23. Database Help    Oracle
  24. Database Help    SQL Server
  25. Database Help    PostgreSQL
  26. Database Help    Access
  27. Database Help    DB2
  28. Sys Admin & OS   Apache
  29. Sys Admin & OS   IIS
  30. Sys Admin & OS   Networking
  31. Sys Admin & OS   Linux / Unix / BSD
  32. Sys Admin & OS   Windows
If you wanted this to be grouped together into lists (as below) then read on.
Expand|Select|Wrap|Line Numbers
  1. Zone             Forums
  2. Community        Introductions, Community Cafe, Software Development, Jobs / Contract Work, Experts Panel
  3. Programming      C++ / C, Java, .NET, Visual Basic, Python
  4. Web Development  PHP, Ruby / Rails, Perl, ASP, ColdFusion, Javascript / Ajax, Flash, XML, WAP / WML, HTML / CSS
  5. Database Help    MySQL, Oracle, SQL Server, PostgreSQL, Access, DB2
  6. Sys Admin & OS   Apache, IIS, Networking, Linux / Unix / BSD, Windows
Firstly, create a function in a standard module similar to the example code attached :
Expand|Select|Wrap|Line Numbers
  1. 'Concat Returns lists of items which are within a grouped field
  2. Public Function Concat(strGroup As String, _
  3.                        strItem As String) As String
  4.     Static strLastGroup As String
  5.     Static strItems As String
  6.  
  7.     If strGroup = strLastGroup Then
  8.         strItems = strItems & ", " & strItem
  9.     Else
  10.         strLastGroup = strGroup
  11.         strItems = strItem
  12.     End If
  13.     Concat = strItems
  14. End Function
The SQL for calling this function would be of the form :
Expand|Select|Wrap|Line Numbers
  1. SELECT [Zone],
  2.   Max(Concat([Zone], [Forum])) AS [Forums]
  3. FROM [tblForum]
  4. GROUP BY [Zone]
Another form of this (to drop duplicates) would be :
Expand|Select|Wrap|Line Numbers
  1. 'Concat Returns lists of items which are within a grouped field
  2. Public Function Concat(strGroup As String, _
  3.                        strItem As String) As String
  4.     Static strLastGroup As String
  5.     Static strItems As String
  6.  
  7.     If strGroup = strLastGroup Then
  8.         If InStr(", " & strItems & ", ", ", " & strItem & ", ") = 0 Then _
  9.             strItems = strItems & ", " & strItem
  10.     Else
  11.         strLastGroup = strGroup
  12.         strItems = strItem
  13.     End If
  14.     Concat = strItems
  15. End Function



Reply

« previous insight | next insight »

Similar Threads
Thread Thread Starter Forum Replies Last Post
List of Values with Select Query Jlayne answers 3 March 24th, 2009 04:19 PM
Info from multiple record in one query field? mandanarchi answers 5 January 28th, 2009 02:13 PM
MS Access Query help - "Spidering" an item somacore answers 5 December 10th, 2008 08:48 PM

/bytes/development

/bytes/it

/bytes/about

We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights. Get the best answers to your questions from over 225,662 network members.

dev questions:
algorithms | asp | asp.net | c/c++ | coldfusion | c# | flash | html/css | java | javascript |
mobile dev | .net | perl | php | python | ruby | software dev | vb.net | visual basic | xml
it questions:
access | apache | careers | db2 | iis | macosx | mysql | networking | oracle | postgresql | sql server | unix | windows

About Bytes | Copyright 1995-2009 BYTES. All rights Reserved