469,347 Members | 20,160 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

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

How do I combine Multiple Rows of data into One Row.

I have designed a Database that will import data and assign values for ordering material. However I am having problems as I currently have to drop this data into an Excel sheet and apply a Pivot Table to it to manipulate the results. I believe that there is a way to do this in Access, however I have been unsuccessful. Hopefully someone out here will be able to assist me. Using some samples I found on other posts here. I have created the following.

Table has data like:

AlphaNumeric WallMark#
AA4 4001
AA4 4008
AB4 4008
AB4 4014

I need it to show like:

AlphaNumeric WallMark#
AA4 4001, 4008.
AB4 4008, 4014.

I have written a Class Module: (as shown here)

================================================== =======
Option Compare Database

Public Function CONCAT(strAlphaNumeric As String, strWallMark#) As String
Static strLastAlphaNumeric As String
Static strPanelInfo As String

If strAlphaNumeric = strLastAlphaNumeric Then
strPanelInfo = strPanelInfo & ", " & strWallMark#
Else
strLastAlphaNumeric = strAlphaNumeric
strPanelInfo = strWallMark#
End If
CONCAT = strPanelInfo

End Function
================================================== =======

And yes I have Compiled and Saved it.


Also, here is the SQL query:

================================================== =======
SELECT AlphaNumeric, Last(CONCAT(AlphaNumeric,[WallMark#])) AS PanelInfo
FROM [TEST TABLE]
GROUP BY AlphaNumeric
================================================== =======

All I keep getting is:

================================================== =======
Undefined function 'CONCAT' in expression.
================================================== =======

Any idea how I can get this to work? I would appreciate any suggestions. Note you can e-mail me at "concathelp@rogers.com".

Thanks.
Jan 16 '08 #1
8 22150
PianoMan64
374 Expert 256MB
I have designed a Database that will import data and assign values for ordering material. However I am having problems as I currently have to drop this data into an Excel sheet and apply a Pivot Table to it to manipulate the results. I believe that there is a way to do this in Access, however I have been unsuccessful. Hopefully someone out here will be able to assist me. Using some samples I found on other posts here. I have created the following.

Table has data like:

AlphaNumeric WallMark#
AA4 4001
AA4 4008
AB4 4008
AB4 4014

I need it to show like:

AlphaNumeric WallMark#
AA4 4001, 4008.
AB4 4008, 4014.

I have written a Class Module: (as shown here)

================================================== =======
Option Compare Database

Public Function CONCAT(strAlphaNumeric As String, strWallMark#) As String
Static strLastAlphaNumeric As String
Static strPanelInfo As String

If strAlphaNumeric = strLastAlphaNumeric Then
strPanelInfo = strPanelInfo & ", " & strWallMark#
Else
strLastAlphaNumeric = strAlphaNumeric
strPanelInfo = strWallMark#
End If
CONCAT = strPanelInfo

End Function
================================================== =======

And yes I have Compiled and Saved it.


Also, here is the SQL query:

================================================== =======
SELECT AlphaNumeric, Last(CONCAT(AlphaNumeric,[WallMark#])) AS PanelInfo
FROM [TEST TABLE]
GROUP BY AlphaNumeric
================================================== =======

All I keep getting is:

================================================== =======
Undefined function 'CONCAT' in expression.
================================================== =======

Any idea how I can get this to work? I would appreciate any suggestions. Note you can e-mail me at "concathelp@rogers.com".

Thanks.
the main reason that is not working it because you have defined it as a class module. It needs to me simply a module. It can't be defined in a form and it can't be defined as a class. You need to put it into a Regular Module, and make it public, then it will work for you.

Hope that helps,

Joe P.
Jan 22 '08 #2
the main reason that is not working it because you have defined it as a class module. It needs to me simply a module. It can't be defined in a form and it can't be defined as a class. You need to put it into a Regular Module, and make it public, then it will work for you.

Hope that helps,

Joe P.
Joe Appreciate your reply...I have made this a Regular Modular and tried again...still no luck.

Any other suggestions?
Jan 22 '08 #3
NeoPa
32,184 Expert Mod 16PB
Check out these threads :
(Combining Multiple Rows of one Field into One Result)
(Combining Rows-Opposite of Union)
I hope you find something there to help.
Jan 22 '08 #4
NeoPa,

Thanks for the information...I have been away for the last few days...I will try it out and get back to you.

MIke
Jan 26 '08 #5
NeoPa
32,184 Expert Mod 16PB
No probs Mike.
I think Cross-Tab queries are another method that might be worth a look at. You decide which suits your needs the closest ;)
Jan 26 '08 #6
NeoPa,

I am still getting the error message. There is only one difference in the Module that I can see. In Mine (as shown below) on line 2.

I tried to have line 2 show as

strWallMark# As String) As String

but it won't let me put in the extra As String as shown in your example. Any idea why it won't?
Expand|Select|Wrap|Line Numbers
  1. Public Function CONCAT(strAlphaNumeric As String, _
  2.                         strWallMark#) As String
  3.  
  4.        Static strLastAlphaNumeric As String
  5.        Static strPanelInfo As String
  6.  
  7.        If strAlphaNumeric = strLastAlphaNumeric Then
  8.             strPanelInfo = strPanelInfo & ", " & strWallMark# 
  9.       Else
  10.            strLastAlphaNumeric = strAlphaNumeric
  11.            strPanelInfo = strWallMark#
  12.        End If
  13.        CONCAT = strPanelInfo
  14.  
  15. End Function
Jan 28 '08 #7
NeoPa
32,184 Expert Mod 16PB
# is a special character in names that forces the variable to be of a particular type. I forget which '#' is, but the character for String is '$'. It is not valid to use these characters in names otherwise. My system's down at the moment (just going) so I can't find which '#' is now. I may find it later and post again.
Jan 28 '08 #8
NeoPa
32,184 Expert Mod 16PB
From http://support.microsoft.com/kb/q191713/ I got :
Expand|Select|Wrap|Line Numbers
  1. Char  Type
  2.  %    Integer
  3.  &    Long
  4.  !    Single
  5.  #    Double
  6.  $    String
  7.  @    Currency
Jan 28 '08 #9

Post your reply

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

Similar topics

reply views Thread by zhoujie | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.