Combine rows in Access 2007

Question

I'm looking for an Access 2007 equivalent to SQL Server's COALESCE function.

In SQL Server you could do something like:

---Person--- 
John
Steve
Richard

DECLARE @PersonList nvarchar(1024)
SELECT @PersonList = COALESCE(@PersonList + ',','') + Person
FROM PersonTable

PRINT @PersonList

Which produces: John, Steve, Richard

I want to do the same but in Access 2007.

Does anyone know how to combine rows like this in Access 2007?

Answer 1

I think Nz is what you're after, syntax is Nz(variant, [if null value]). Here's the documentation link: Nz Function

---Person--- 
John
Steve
Richard

DECLARE @PersonList nvarchar(1024)
SELECT @PersonList = Nz(@PersonList + ',','') + Person
FROM PersonTable

PRINT @PersonList

Answer 2

Although Nz does a comparable thing to COALESCE, you can't use it in Access to do the operation you are performing. It's not the COALESCE that is building the list of row values, it's the concatenatiion into a variable.

Unfortunately, this isn't possible inside an Access query which has to be a single SQL statement and where there is no facility to declare a variable.

I think you would need to create a function that would open a resultset, iterate over it and concatenate the row values into a string.

Answer 3

To combine rows in Access, you'll probably need code that looks something like this:

Public Function Coalesce(pstrTableName As String, pstrFieldName As String)

Dim rst As DAO.Recordset
Dim str As String

    Set rst = CurrentDb.OpenRecordset(pstrTableName)
    Do While rst.EOF = False
        If Len(str) = 0 Then
            str = rst(pstrFieldName)
        Else
            str = str & "," & rst(pstrFieldName)
        End If
        rst.MoveNext
    Loop

    Coalesce = str

End Function

You'll want to add error-handling code and clean up your recordset, and this will change slightly if you use ADO instead of DAO, but the general idea is the same.

Answer 4

I understand here that you have a table "person" with 3 records. There is nothing comparable to what you describe in Access.

In "standard" Access (DAO recordset), you will have to open a recordset and use the getrows method to have your data

Dim rs as DAO.recordset, _
    personList as String, _
    personArray() as variant

set rs = currentDb.open("Person")
set personArray = rs.getRows(rs.recordcount)

rs.close

once you have this array (it will be bidimensional), you can manipulate it to extract the "column" you'll need. There might be a smart way to extract a one-dimension array from this, so you can then use the "Join" instruction to concatenate each array value in one string.

Answer 5

Here is a sample User Defined Function (UDF) and possible usage.

Function:

Function Coalsce(strSQL As String, strDelim, ParamArray NameList() As Variant)
Dim db As Database
Dim rs As DAO.Recordset
Dim strList As String

    Set db = CurrentDb

    If strSQL <> "" Then
        Set rs = db.OpenRecordset(strSQL)

        Do While Not rs.EOF
            strList = strList & strDelim & rs.Fields(0)
            rs.MoveNext
        Loop

        strList = Mid(strList, Len(strDelim))
    Else

        strList = Join(NameList, strDelim)
    End If

    Coalsce = strList

End Function

Usage:

SELECT documents.MembersOnly, 
    Coalsce("SELECT FName From Persons WHERE Member=True",":") AS Who, 
    Coalsce("",":","Mary","Joe","Pat?") AS Others
FROM documents;