469,315 Members | 1,802 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,315 developers. It's quick & easy.

Problem combining multiple rows into one

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.

7 1731
NeoPa
32,173 Expert Mod 16PB
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
almaroc
48
Thanks NeoPa. it worked perfectly.
Jan 12 '12 #3
pod
298 100+
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
12,516 Expert Mod 8TB
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
32,173 Expert Mod 16PB
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
298 100+
"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
32,173 Expert Mod 16PB
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.

Similar topics

7 posts views Thread by RotterdamStudents | last post: by
3 posts views Thread by manjub | last post: by
5 posts views Thread by BerkshireGuy | last post: by
1 post views Thread by Nipun Valoor | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by harlem98 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.