Problem combining multiple rows into one

[almaroc]

i am using access 2007 and i am trying to combine multiple rows into one. i am using the code from this site but i continue to get this error:
Compile error in query expression. last(concat(dat a1.company,data 1.esi))
i tried using max instead of last same error however.

Public Function Concat(strCOMPANY As String, _
      strESI As String) As String
      Static strLastCOMPANY As String
      Static strESI As String

      If strCOMPANY = strLastCOMPANY Then
         strESI = strESI & ", " & strESI
     Else
         strLastCOMPANY = strCOMPANY
         strESI = strESI
     End If
     Concat = strESI
 End Function

my query is as follows

SELECT data1.COMPANY, Last(Concat(data1.[COMPANY],data1.[ESI])) AS ESIs
FROM data1
GROUP BY data1.[COMPANY];

[NeoPa]

It looks like you've got something from Producing a List from Multiple Records but not followed the instructions very well. You should be working with Max() (and posting that code properly - NOT typing it in).

That said, I would expect, assuming the data fits the required format, that it would work.

[almaroc]

Thanks NeoPa. it worked perfectly.

[pod]

is there anyway to do this without the use of some VBA function call? I mean, to do it entirely within an SQL statement, ... no modules, just plain SQL

[Rabbit]

Not with Jet SQL no. In DB2 and SQL Server you can use the built in XML functionality. In MySQL you can use a variable.

[NeoPa]

It's a somewhat unnatural request to be fair Pod. SQL and RDBMSs are based on set theory, in which positioning is not relevant. Various systems nevertheless supply such functionality (as indicated by Rabbit's post).

[pod]

"Various systems" always seem to be excluding Microsoft.Jet :)

The reason for my request is that I always try to put as much of the work within the SQL statement, to get as much done as possible before the rest of the code within tool takes over... but it looks like I will have to add a function ...

[NeoPa]

Actually, Jet tends more towards providing extra towards those whose thinking is less than pure (as in pure set theory). In this case though, while it does provides the Cross-tab query, that's as far as it goes as far as I know. So, ultimately, you're still out of luck there I'm afraid.