Concatenate Values in Crosstab Query

  • Thread starter Thread starter Jeff H
  • Start date Start date
J

Jeff H

I have a crosstab query client activities with the activity type as the value.

The crosstab is based on the following Table of open client
activities(sample data):

Recipient ID;Activity ID;Activity Type;Start Date;Planned Hours;Actual
Wages;Status;Ordinal
011005556823;A/P-0044661;756;12-Mar-09;18; ;Open;1
011005556823;A/P-0044662;755;12-Mar-09;2; ;Open;2
011005556823;A/P-0044663;757;12-Mar-09;10; ;Open;3
000000029718;A/P-0064856;754;07-Jul-09;30; ;Open;1
000715913950;A/P-0052960;757;04-May-09;10; ;Open;1
000715913950;A/P-0052961;755;04-May-09;9; ;Open;2

NOTE: The ordinal values for any client are variable, i.e. any client may
have any number of open activities.

The results of the crosstab:
Recipient ID;1;2;3
011005556823;756;755;757
000000029718;754
000715913950;757;755

I need an additional row heading concatenating the activity types.
Recipient ID;Open Activity Types
011005556823;756-755-757
000000029718;754
000715913950;757-755

Thanks.
 
Thanks, Duane. It almost works for me but I'm getting an error on the
function. I'm only a self-taught VBA'er. I copied and pasted the function
from the sample database into a new module.

This is what I have for the function:
Activity Types: Concatenate("SELECT [Activity Type] FROM [All Open
Activities] WHERE [Recipient ID] =" & [Recipient ID])

When I run the query, I get error: "Data type mismatch in criteria
expression."

Debug takes me to the following line of code:
rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic

Any ideas on how to fix...what I've done wrong...or what I'm passing to the
function wrong? Thanks.
 
It looks like Recipient ID is text. If this is the case, then try:
Activity Types: Concatenate("SELECT [Activity Type] FROM [All Open
Activities] WHERE [Recipient ID] ='" & [Recipient ID] & "'")

--
Duane Hookom
Microsoft Access MVP


Jeff H said:
Thanks, Duane. It almost works for me but I'm getting an error on the
function. I'm only a self-taught VBA'er. I copied and pasted the function
from the sample database into a new module.

This is what I have for the function:
Activity Types: Concatenate("SELECT [Activity Type] FROM [All Open
Activities] WHERE [Recipient ID] =" & [Recipient ID])

When I run the query, I get error: "Data type mismatch in criteria
expression."

Debug takes me to the following line of code:
rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic

Any ideas on how to fix...what I've done wrong...or what I'm passing to the
function wrong? Thanks.



Duane Hookom said:
There is a generic concatenate function with sample usage at
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=16.
Click to expand...
Click to expand...
 
BINGO! That worked. Thank you so much. Have a virtual espresso on me. Pfft,
make it a virtual margarita!

Duane Hookom said:
It looks like Recipient ID is text. If this is the case, then try:
Activity Types: Concatenate("SELECT [Activity Type] FROM [All Open
Activities] WHERE [Recipient ID] ='" & [Recipient ID] & "'")

--
Duane Hookom
Microsoft Access MVP


Jeff H said:
Thanks, Duane. It almost works for me but I'm getting an error on the
function. I'm only a self-taught VBA'er. I copied and pasted the function
from the sample database into a new module.

This is what I have for the function:
Activity Types: Concatenate("SELECT [Activity Type] FROM [All Open
Activities] WHERE [Recipient ID] =" & [Recipient ID])

When I run the query, I get error: "Data type mismatch in criteria
expression."

Debug takes me to the following line of code:
rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic

Any ideas on how to fix...what I've done wrong...or what I'm passing to the
function wrong? Thanks.



Duane Hookom said:
There is a generic concatenate function with sample usage at
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=16.

--
Duane Hookom
Microsoft Access MVP


:

I have a crosstab query client activities with the activity type as the value.

The crosstab is based on the following Table of open client
activities(sample data):

Recipient ID;Activity ID;Activity Type;Start Date;Planned Hours;Actual
Wages;Status;Ordinal
011005556823;A/P-0044661;756;12-Mar-09;18; ;Open;1
011005556823;A/P-0044662;755;12-Mar-09;2; ;Open;2
011005556823;A/P-0044663;757;12-Mar-09;10; ;Open;3
000000029718;A/P-0064856;754;07-Jul-09;30; ;Open;1
000715913950;A/P-0052960;757;04-May-09;10; ;Open;1
000715913950;A/P-0052961;755;04-May-09;9; ;Open;2

NOTE: The ordinal values for any client are variable, i.e. any client may
have any number of open activities.

The results of the crosstab:
Recipient ID;1;2;3
011005556823;756;755;757
000000029718;754
000715913950;757;755

I need an additional row heading concatenating the activity types.
Recipient ID;Open Activity Types
011005556823;756-755-757
000000029718;754
000715913950;757-755

Thanks.
Click to expand...
Click to expand...
Click to expand...
 
Cheers!
--
Duane Hookom
Microsoft Access MVP


Jeff H said:
BINGO! That worked. Thank you so much. Have a virtual espresso on me. Pfft,
make it a virtual margarita!

Duane Hookom said:
It looks like Recipient ID is text. If this is the case, then try:
Activity Types: Concatenate("SELECT [Activity Type] FROM [All Open
Activities] WHERE [Recipient ID] ='" & [Recipient ID] & "'")

--
Duane Hookom
Microsoft Access MVP


Jeff H said:
Thanks, Duane. It almost works for me but I'm getting an error on the
function. I'm only a self-taught VBA'er. I copied and pasted the function
from the sample database into a new module.

This is what I have for the function:
Activity Types: Concatenate("SELECT [Activity Type] FROM [All Open
Activities] WHERE [Recipient ID] =" & [Recipient ID])

When I run the query, I get error: "Data type mismatch in criteria
expression."

Debug takes me to the following line of code:
rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic

Any ideas on how to fix...what I've done wrong...or what I'm passing to the
function wrong? Thanks.



:

There is a generic concatenate function with sample usage at
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=16.

--
Duane Hookom
Microsoft Access MVP


:

I have a crosstab query client activities with the activity type as the value.

The crosstab is based on the following Table of open client
activities(sample data):

Recipient ID;Activity ID;Activity Type;Start Date;Planned Hours;Actual
Wages;Status;Ordinal
011005556823;A/P-0044661;756;12-Mar-09;18; ;Open;1
011005556823;A/P-0044662;755;12-Mar-09;2; ;Open;2
011005556823;A/P-0044663;757;12-Mar-09;10; ;Open;3
000000029718;A/P-0064856;754;07-Jul-09;30; ;Open;1
000715913950;A/P-0052960;757;04-May-09;10; ;Open;1
000715913950;A/P-0052961;755;04-May-09;9; ;Open;2

NOTE: The ordinal values for any client are variable, i.e. any client may
have any number of open activities.

The results of the crosstab:
Recipient ID;1;2;3
011005556823;756;755;757
000000029718;754
000715913950;757;755

I need an additional row heading concatenating the activity types.
Recipient ID;Open Activity Types
011005556823;756-755-757
000000029718;754
000715913950;757-755

Thanks.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top