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

Problem combining multiple rows into one

P: 48
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(data1.company,data1.esi))
i tried using max instead of last same error however.
Expand|Select|Wrap|Line Numbers
  1. Public Function Concat(strCOMPANY As String, _
  2.       strESI As String) As String
  3.       Static strLastCOMPANY As String
  4.       Static strESI As String
  5.  
  6.       If strCOMPANY = strLastCOMPANY Then
  7.          strESI = strESI & ", " & strESI
  8.      Else
  9.          strLastCOMPANY = strCOMPANY
  10.          strESI = strESI
  11.      End If
  12.      Concat = strESI
  13.  End Function
  14.  
my query is as follows
Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT data1.COMPANY, Last(Concat(data1.[COMPANY],data1.[ESI])) AS ESIs
  3. FROM data1
  4. GROUP BY data1.[COMPANY];
  5.  
Jan 11 '12 #1

✓ answered by 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.

Share this Question
Share on Google+
7 Replies


NeoPa
Expert Mod 15k+
P: 31,494
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.
Jan 11 '12 #2

P: 48
Thanks NeoPa. it worked perfectly.
Jan 12 '12 #3

pod
100+
P: 298
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
Jan 12 '12 #4

Rabbit
Expert Mod 10K+
P: 12,366
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.
Jan 12 '12 #5

NeoPa
Expert Mod 15k+
P: 31,494
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).
Jan 12 '12 #6

pod
100+
P: 298
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 ...
Jan 12 '12 #7

NeoPa
Expert Mod 15k+
P: 31,494
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.
Jan 12 '12 #8

Post your reply

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