Concatenation Question

[Guest]

I am developing a student evaluation database. One table has questionnaire
responses across multiple courses. I want to produce aggregate statistics for
each course.

I don't have a problem producing stats for the multiple choice items for an
individual course. However, there are four text fields (e.g. "What do you
like most about this course?") In the summary report, I would like to "roll
up" or concatentate all student responses to a particular text question into
one big memo field.

I presume this would be done in a query if it can be done at all. Any help
appreciated.

 

[Duane Hookom]

There is a generic concatenate function with sample usage at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

 

[Guest]

Looks exactly like what I need. But follow-up question: the concatenated
string is quite likely to exceed 255 characters. Will the 256-n characters be
truncated?

 

[Guest]

Never mind. It seems the answer to my question is NO, no truncation going on.
Thought I read about that being a limitation somewhere. Again, thanks.

 

[Guest]

Hi Duane, thanks for the function. I have a table (tbFam):
LastName FirstName
a x
a y
a z
b x1
b y2
c x3
c y3
I used your sql statement and concat function:

SELECT tblFam.LastName, Concatenate("SELECT FirstName FROM tblFam WHERE
LastName =" & [LastName]) AS FirstNames
FROM tblFam;

But I got Run-time error for no value given for required parameter. Debug
shows stop at
rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic

Any reasons?

Thanks
Howard

 

[Duane Hookom]

You need to understand the difference between using numeric and string
values:
SELECT tblFam.LastName, Concatenate("SELECT FirstName FROM tblFam WHERE
LastName =""" & [LastName] & """") AS FirstNames
FROM tblFam;

--
Duane Hookom
MS Access MVP

Hi Duane, thanks for the function. I have a table (tbFam):
LastName FirstName
a x
a y
a z
b x1
b y2
c x3
c y3
I used your sql statement and concat function:

SELECT tblFam.LastName, Concatenate("SELECT FirstName FROM tblFam WHERE
LastName =" & [LastName]) AS FirstNames
FROM tblFam;

But I got Run-time error for no value given for required parameter. Debug
shows stop at
rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic

Any reasons?

Thanks
Howard
--
Learning ACCESS

There is a generic concatenate function with sample usage at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

 

[Guest]

Hi Duane,
Thanks for the correction.
Since the original field Data type for concatenation is MEMO, I thought
after concatenation, the data type should also be MEMO. But I saw 255
character limit, while John V did not. Any reason and ways to correct this?

Thanks.
Howard
--
Learning ACCESS

You need to understand the difference between using numeric and string
values:
SELECT tblFam.LastName, Concatenate("SELECT FirstName FROM tblFam WHERE
LastName =""" & [LastName] & """") AS FirstNames
FROM tblFam;

--
Duane Hookom
MS Access MVP

Hi Duane, thanks for the function. I have a table (tbFam):
LastName FirstName
a x
a y
a z
b x1
b y2
c x3
c y3
I used your sql statement and concat function:

SELECT tblFam.LastName, Concatenate("SELECT FirstName FROM tblFam WHERE
LastName =" & [LastName]) AS FirstNames
FROM tblFam;

But I got Run-time error for no value given for required parameter. Debug
shows stop at
rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic

Any reasons?

Thanks
Howard
--
Learning ACCESS

There is a generic concatenate function with sample usage at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

--
Duane Hookom
MS Access MVP

I am developing a student evaluation database. One table has
questionnaire
responses across multiple courses. I want to produce aggregate
statistics
for
each course.

I don't have a problem producing stats for the multiple choice items
for
an
individual course. However, there are four text fields (e.g. "What do
you
like most about this course?") In the summary report, I would like to
"roll
up" or concatentate all student responses to a particular text question
into
one big memo field.

I presume this would be done in a query if it can be done at all. Any
help
appreciated.

 

[Duane Hookom]

Correct what? What field is a memo field? What problem are you having? Are
you getting an error message? Which field do you consider "the original
field"?

--
Duane Hookom
MS Access MVP

Hi Duane,
Thanks for the correction.
Since the original field Data type for concatenation is MEMO, I thought
after concatenation, the data type should also be MEMO. But I saw 255
character limit, while John V did not. Any reason and ways to correct
this?

Thanks.
Howard
--
Learning ACCESS

You need to understand the difference between using numeric and string
values:
SELECT tblFam.LastName, Concatenate("SELECT FirstName FROM tblFam WHERE
LastName =""" & [LastName] & """") AS FirstNames
FROM tblFam;

--
Duane Hookom
MS Access MVP

Hi Duane, thanks for the function. I have a table (tbFam):
LastName FirstName
a x
a y
a z
b x1
b y2
c x3
c y3
I used your sql statement and concat function:

SELECT tblFam.LastName, Concatenate("SELECT FirstName FROM tblFam WHERE
LastName =" & [LastName]) AS FirstNames
FROM tblFam;

But I got Run-time error for no value given for required parameter.
Debug
shows stop at
rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic

Any reasons?

Thanks
Howard
--
Learning ACCESS


:

There is a generic concatenate function with sample usage at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

--
Duane Hookom
MS Access MVP

I am developing a student evaluation database. One table has
questionnaire
responses across multiple courses. I want to produce aggregate
statistics
for
each course.

I don't have a problem producing stats for the multiple choice items
for
an
individual course. However, there are four text fields (e.g. "What
do
you
like most about this course?") In the summary report, I would like
to
"roll
up" or concatentate all student responses to a particular text
question
into
one big memo field.

I presume this would be done in a query if it can be done at all.
Any
help
appreciated.

 

[Guest]

Hi Duane,
Sorry that I was not very clear. I wanted to use your function in a
different table, say tblComments, with 2 fields: Name, Comment. I wanted to
concatenate different comments for a unique Name, and the output of the query
will have two fields: Name, AllComments. The "Comment" field data type is
MEMO, should the "AllComments" field also be MEMO? But I got 255 character
limit on the "AllComments" query result

SELECT tblComments.Name, Concatenate("SELECT Comment FROM tblComments WHERE
Name =""" & [Name] & """") AS AllComments
FROM tblComments;

-Howard
--
Learning ACCESS

Correct what? What field is a memo field? What problem are you having? Are
you getting an error message? Which field do you consider "the original
field"?

Duane Hookom
MS Access MVP

Hi Duane,
Thanks for the correction.
Since the original field Data type for concatenation is MEMO, I thought
after concatenation, the data type should also be MEMO. But I saw 255
character limit, while John V did not. Any reason and ways to correct
this?

Thanks.
Howard
--
Learning ACCESS

You need to understand the difference between using numeric and string
values:
SELECT tblFam.LastName, Concatenate("SELECT FirstName FROM tblFam WHERE
LastName =""" & [LastName] & """") AS FirstNames
FROM tblFam;

--
Duane Hookom
MS Access MVP

Hi Duane, thanks for the function. I have a table (tbFam):
LastName FirstName
a x
a y
a z
b x1
b y2
c x3
c y3
I used your sql statement and concat function:

SELECT tblFam.LastName, Concatenate("SELECT FirstName FROM tblFam WHERE
LastName =" & [LastName]) AS FirstNames
FROM tblFam;

But I got Run-time error for no value given for required parameter.
Debug
shows stop at
rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic

Any reasons?

Thanks
Howard
--
Learning ACCESS


:

There is a generic concatenate function with sample usage at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

--
Duane Hookom
MS Access MVP

I am developing a student evaluation database. One table has
questionnaire
responses across multiple courses. I want to produce aggregate
statistics
for
each course.

I don't have a problem producing stats for the multiple choice items
for
an
individual course. However, there are four text fields (e.g. "What
do
you
like most about this course?") In the summary report, I would like
to
"roll
up" or concatentate all student responses to a particular text
question
into
one big memo field.

I presume this would be done in a query if it can be done at all.
Any
help
appreciated.

 

[Duane Hookom]

I don't know why the datasheet view of the query would not show more than
255 characters. I expect there is something you aren't telling us about the
query or how you are using it.

Your query results would repeat the AllComments possibly many times per
Name. Is that what you are seeing?

--
Duane Hookom
MS Access MVP

Hi Duane,
Sorry that I was not very clear. I wanted to use your function in a
different table, say tblComments, with 2 fields: Name, Comment. I wanted
to
concatenate different comments for a unique Name, and the output of the
query
will have two fields: Name, AllComments. The "Comment" field data type is
MEMO, should the "AllComments" field also be MEMO? But I got 255 character
limit on the "AllComments" query result

SELECT tblComments.Name, Concatenate("SELECT Comment FROM tblComments
WHERE
Name =""" & [Name] & """") AS AllComments
FROM tblComments;

-Howard
--
Learning ACCESS

Correct what? What field is a memo field? What problem are you having?
Are
you getting an error message? Which field do you consider "the original
field"?

Duane Hookom
MS Access MVP

Hi Duane,
Thanks for the correction.
Since the original field Data type for concatenation is MEMO, I thought
after concatenation, the data type should also be MEMO. But I saw 255
character limit, while John V did not. Any reason and ways to correct
this?

Thanks.
Howard
--
Learning ACCESS


:

You need to understand the difference between using numeric and string
values:
SELECT tblFam.LastName, Concatenate("SELECT FirstName FROM tblFam
WHERE
LastName =""" & [LastName] & """") AS FirstNames
FROM tblFam;

--
Duane Hookom
MS Access MVP

Hi Duane, thanks for the function. I have a table (tbFam):
LastName FirstName
a x
a y
a z
b x1
b y2
c x3
c y3
I used your sql statement and concat function:

SELECT tblFam.LastName, Concatenate("SELECT FirstName FROM tblFam
WHERE
LastName =" & [LastName]) AS FirstNames
FROM tblFam;

But I got Run-time error for no value given for required parameter.
Debug
shows stop at
rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic

Any reasons?

Thanks
Howard
--
Learning ACCESS


:

There is a generic concatenate function with sample usage at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

--
Duane Hookom
MS Access MVP

I am developing a student evaluation database. One table has
questionnaire
responses across multiple courses. I want to produce aggregate
statistics
for
each course.

I don't have a problem producing stats for the multiple choice
items
for
an
individual course. However, there are four text fields (e.g.
"What
do
you
like most about this course?") In the summary report, I would
like
to
"roll
up" or concatentate all student responses to a particular text
question
into
one big memo field.

I presume this would be done in a query if it can be done at all.
Any
help
appreciated.

 

[Guest]

I did not see repeat of AllComments. But I did use SELECT DISTINCT see the
255 limit. After removing DISTINCT, everything is fine now.
Many thanks.

Howard
--
Learning ACCESS

I don't know why the datasheet view of the query would not show more than
255 characters. I expect there is something you aren't telling us about the
query or how you are using it.

Your query results would repeat the AllComments possibly many times per
Name. Is that what you are seeing?

--
Duane Hookom
MS Access MVP

Hi Duane,
Sorry that I was not very clear. I wanted to use your function in a
different table, say tblComments, with 2 fields: Name, Comment. I wanted
to
concatenate different comments for a unique Name, and the output of the
query
will have two fields: Name, AllComments. The "Comment" field data type is
MEMO, should the "AllComments" field also be MEMO? But I got 255 character
limit on the "AllComments" query result

SELECT tblComments.Name, Concatenate("SELECT Comment FROM tblComments
WHERE
Name =""" & [Name] & """") AS AllComments
FROM tblComments;

-Howard
--
Learning ACCESS

Correct what? What field is a memo field? What problem are you having?
Are
you getting an error message? Which field do you consider "the original
field"?

Duane Hookom
MS Access MVP

Hi Duane,
Thanks for the correction.
Since the original field Data type for concatenation is MEMO, I thought
after concatenation, the data type should also be MEMO. But I saw 255
character limit, while John V did not. Any reason and ways to correct
this?

Thanks.
Howard
--
Learning ACCESS


:

You need to understand the difference between using numeric and string
values:
SELECT tblFam.LastName, Concatenate("SELECT FirstName FROM tblFam
WHERE
LastName =""" & [LastName] & """") AS FirstNames
FROM tblFam;

--
Duane Hookom
MS Access MVP

Hi Duane, thanks for the function. I have a table (tbFam):
LastName FirstName
a x
a y
a z
b x1
b y2
c x3
c y3
I used your sql statement and concat function:

SELECT tblFam.LastName, Concatenate("SELECT FirstName FROM tblFam
WHERE
LastName =" & [LastName]) AS FirstNames
FROM tblFam;

But I got Run-time error for no value given for required parameter.
Debug
shows stop at
rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic

Any reasons?

Thanks
Howard
--
Learning ACCESS


:

There is a generic concatenate function with sample usage at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

--
Duane Hookom
MS Access MVP

I am developing a student evaluation database. One table has
questionnaire
responses across multiple courses. I want to produce aggregate
statistics
for
each course.

I don't have a problem producing stats for the multiple choice
items
for
an
individual course. However, there are four text fields (e.g.
"What
do
you
like most about this course?") In the summary report, I would
like
to
"roll
up" or concatentate all student responses to a particular text
question
into
one big memo field.

I presume this would be done in a query if it can be done at all.
Any
help
appreciated.