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

[Mike Reid]

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 "[e-mail]".

Thanks.

[PianoMan64]

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 "[e-mail]".

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.

[Mike Reid]

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?

[NeoPa]

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.

[Mike Reid]

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

[NeoPa]

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 ;)

[Mike Reid]

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?

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

[NeoPa]

# 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.

[NeoPa]

From http://support.microsoft.com/kb/q191713/ I got :

Char  Type
 %    Integer
 &    Long
 !    Single
 #    Double
 $    String
 @    Currency