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

[OwenZurich]

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

[jforbes]

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:

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)
 
    ' Iterates through all the records of the specified table,
    ' using the sCriteria as a where clause
    ' and concatenates the specified columns into a single string
 
    Dim sSQL As String
    Dim oRst As DAO.Recordset
    Dim sNewText As String
    Dim sReturn As String
 
    sSQL = sSQL & "SELECT "
    If bDistinct Then sSQL = sSQL & "DISTINCT "
    sSQL = sSQL & sColumn & " FROM " & sTable & " WHERE " & sCriteria
    Set oRst = CurrentDb.OpenRecordset(sSQL, dbOpenForwardOnly)
    Do While Not oRst.EOF
        sNewText = Nz(oRst(sColumn), "")
        If Len(sNewText) > 0 Then sReturn = sReturn & sNewText & sDelimiter
        oRst.MoveNext
    Loop
 
    If Len(sReturn) > 0 Then sReturn = Left(sReturn, Len(sReturn) - Len(sDelimiter))
    If iMaxLength > 0 And Len(sReturn) > iMaxLength Then sReturn = Left(sReturn, iMaxLength)
 
    getRowsIntoColumn = sReturn
End Function

To use this, build a Query something like this:

SELECT CustomerID, CustomerName, getRowsIntoColumn("Policies", "PolicyID", "CustomerID='" & [CustomerID] & "'", ", ", True) AS Policies FROM Customers