By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,607 Members | 1,978 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,607 IT Pros & Developers. It's quick & easy.

How to join data from one table field together to output as a string

P: 1
I have a table where we hold ID's for a particular customer and for each year the customer renews the contract we use the same number. I need to be able to output as a single string all the unique ID's with a semi colon inbetween for transfer into another system but I do not know how to do this in a query.
I'm assuming the first stage would be to build a query that groups the ID's together but how to join them together after that is the problem.
Example:
Policy 1 2014 ID1
Policy 1 2015 ID1
Policy 2 2015 ID2
Policy 3 2014 ID3
Policy 3 2015 ID3
becomes ID1;ID2;ID3
May 18 '15 #1
Share this Question
Share on Google+
1 Reply


jforbes
Expert 100+
P: 1,107
On SQL Server there are a couple ways to do this, but there is not an easy, strictly SQL way of doing this in Access. In my experience in Access, a function needs to be used.

You can check out these threads for a solution:
http://bytes.com/topic/access/answer...opposite-union
and http://bytes.com/topic/access/answer...nto-one-result

An alternate solution I've used, which is similar to the ones above, is the following function:
Expand|Select|Wrap|Line Numbers
  1. Public Function getRowsIntoColumn(ByVal sTable As String, ByVal sColumn As String, ByVal sCriteria As String, ByVal sDelimiter As String, Optional bDistinct As Boolean = False, Optional iMaxLength As Integer = 0)
  2.  
  3.     ' Iterates through all the records of the specified table,
  4.     ' using the sCriteria as a where clause
  5.     ' and concatenates the specified columns into a single string
  6.  
  7.     Dim sSQL As String
  8.     Dim oRst As DAO.Recordset
  9.     Dim sNewText As String
  10.     Dim sReturn As String
  11.  
  12.     sSQL = sSQL & "SELECT "
  13.     If bDistinct Then sSQL = sSQL & "DISTINCT "
  14.     sSQL = sSQL & sColumn & " FROM " & sTable & " WHERE " & sCriteria
  15.     Set oRst = CurrentDb.OpenRecordset(sSQL, dbOpenForwardOnly)
  16.     Do While Not oRst.EOF
  17.         sNewText = Nz(oRst(sColumn), "")
  18.         If Len(sNewText) > 0 Then sReturn = sReturn & sNewText & sDelimiter
  19.         oRst.MoveNext
  20.     Loop
  21.  
  22.     If Len(sReturn) > 0 Then sReturn = Left(sReturn, Len(sReturn) - Len(sDelimiter))
  23.     If iMaxLength > 0 And Len(sReturn) > iMaxLength Then sReturn = Left(sReturn, iMaxLength)
  24.  
  25.     getRowsIntoColumn = sReturn
  26. End Function
To use this, build a Query something like this:
Expand|Select|Wrap|Line Numbers
  1. SELECT CustomerID, CustomerName, getRowsIntoColumn("Policies", "PolicyID", "CustomerID='" & [CustomerID] & "'", ", ", True) AS Policies FROM Customers
May 18 '15 #2

Post your reply

Sign in to post your reply or Sign up for a free account.