Concatenate Values in Crosstab Query

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

Jeff H

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

Duane Hookom

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

Jeff H

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

Duane Hookom

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Crosstab Query based on Ordinal Values 6

Top