Problem with concatenating multiple fields and records

[Isaac Martin]

I am fairly new to using Access and have recently completed the
creation of a database used to track individual audit results. The
audit has 28 questions, with the possiblity of leaving 2 different
comments for each question, a scoring comment or a coaching comment. I
am trying to build a query to concatenate the comments for each
question. If successful the plan is to use the query to generate a
report showing all of the scoring and coaching comments produced
within a given time period. I have been trying to start small, and
just concatenate the scoring comments for one question. I have two
separate tables which I need to relate to each other. One is named "QA
MSTM" and holds information for each individual who will need to be
audited associated with a work site and manager. The other table is
named "QA Evaluation Form" and stores the actual audit results. These
two tables are joined by a relationship which identifies the
individual being audited with the employee number common to both
tables.

I need to organize the query based on the field "Site", which is a
text field. I want the query to concatenate the comments for each site
using the field "1:01 Scoring Comment", which is a memo field (as are
all of the other comments which will also need to be concatenated).

I downloaded the example database created by Duane Hookom with the
concatenation examples based on family names and roles and imported
the module for the concatenation function into my db, but when
attempting to duplicate the example query using my tables and fields I
am receiving the error "no value given for one or more required
parameters". I have looked through many solutions offered in the
various groups, and none seem to work in my case.

As I am fairly new to access I am not completely familiar with the
appropriate terminology or sql syntax. Any help that could be offered
would be greatly appreciated!

 

[Isaac Martin]

I am fairly new to using Access and have recently completed the
creation of a database used to track individual audit results. The
audit has 28 questions, with the possiblity of leaving 2 different
comments for each question, a scoring comment or a coaching comment. I
am trying to build a query to concatenate the comments for each
question. If successful the plan is to use the query to generate a
report showing all of the scoring and coaching comments produced
within a given time period. I have been trying to start small, and
just concatenate the scoring comments for one question. I have two
separate tables which I need to relate to each other. One is named "QA
MSTM" and holds information for each individual who will need to be
audited associated with a work site and manager. The other table is
named "QA Evaluation Form" and stores the actual audit results. These
two tables are joined by a relationship which identifies the
individual being audited with the employee number common to both
tables.

I need to organize the query based on the field "Site", which is a
text field. I want the query to concatenate the comments for each site
using the field "1:01 Scoring Comment", which is a memo field (as are
all of the other comments which will also need to be concatenated).

I downloaded the example database created by Duane Hookom with the
concatenation examples based on family names and roles and imported
the module for the concatenation function into my db, but when
attempting to duplicate the example query using my tables and fields I
am receiving the error "no value given for one or more required
parameters". I have looked through many solutions offered in the
various groups, and none seem to work in my case.

As I am fairly new to access I am not completely familiar with the
appropriate terminology or sql syntax. Any help that could be offered
would be greatly appreciated!

I should also note that I am using Access 2002 and 2003 (depending on
the desk I am sitting at on a given day).

 

[BruceM]

Duane's concatenation example involves concatenating the same field from
several records. It sounds as if you are doing something else.

What do you mean by:
I want the query to concatenate the comments for each site using the field
"1:01 Scoring Comment", which is a memo field

It sounds as if you are trying to store the result. In most cases you will
not store a calculation (including concatenation). When you open the query
in datasheet view, does a single row contain all of the comments you want to
concatenate? If so, switch to query design view. At the top of a new
column put something like:
NewField: [Comment1] & chr(13) & chr(10) & [Comment2], etc.
chr(13) and chr(10) will move you to a new line for Comment2, in case you
want that.

If this doesn't help, a little more detail is needed, including a brief
description of the table structure (with key fields and just a few typical
fields).

 

[Isaac Martin]

Duane's concatenation example involves concatenating the same field from
several records. It sounds as if you are doing something else.

What do you mean by:
I want the query to concatenate the comments for each site using the field
"1:01 Scoring Comment", which is a memo field

It sounds as if you are trying to store the result. In most cases you will
not store a calculation (including concatenation). When you open the query
in datasheet view, does a single row contain all of the comments you want to
concatenate? If so, switch to query design view. At the top of a new
column put something like:
NewField: [Comment1] & chr(13) & chr(10) & [Comment2], etc.
chr(13) and chr(10) will move you to a new line for Comment2, in case you
want that.

If this doesn't help, a little more detail is needed, including a brief
description of the table structure (with key fields and just a few typical
fields).

My table holds unique records for each audit performed (currently over
300 records). Each record represents a single audit for an employee at
one of two sites. What I am hoping to do is bring all of the comments
recorded in the field [1:01 Scoring Comment] into a single field. So,
I would be able to see all of the comments for a given site in one
record line. Right now I have over 300 separate records with their own
comments. If I use the expression you suggested I can only group the
comments for an individual record, and not all of the comments from
all of the records.

As far as my table structure, it records things such as employee name,
employee number, manager name, site name, score for each question,
etc. In this case all I want to do is pull out the comments made by
the auditors, so that I can produce a report showing all of teh audit
comments made for all of the audits performed for employees at a
specific work site.

The full record set is actually contained in a query, due to a quirk
in the initial development. The site name, manager name, and employee
name are coming from the QA MSTM table, while the remaining fields are
coming from the QA Evaluation form table. These two tables are joined
through the employee number, which is common to both tables. This may
be part of my problem, considering the way the concatenation
expression is written. I am not sure how to write the query. In
essence, I only want the site names to appear in column 1, and in
column two I want to see all of the comments that were recorded. I am
hoping to generate only two rows, one for each site.

My apologies if I am being unclear, I am pretty new to this and
haven't quite gotten a handle on how to articulate my work in Access.
Thanks for your help!

 

[Isaac Martin]

Duane's concatenation example involves concatenating the same field from
several records. It sounds as if you are doing something else.

What do you mean by:
I want the query to concatenate the comments for each site using the field
"1:01 Scoring Comment", which is a memo field

It sounds as if you are trying to store the result. In most cases you will
not store a calculation (including concatenation). When you open the query
in datasheet view, does a single row contain all of the comments you want to
concatenate? If so, switch to query design view. At the top of a new
column put something like:
NewField: [Comment1] & chr(13) & chr(10) & [Comment2], etc.
chr(13) and chr(10) will move you to a new line for Comment2, in case you
want that.

If this doesn't help, a little more detail is needed, including a brief
description of the table structure (with key fields and just a few typical
fields).

My table holds unique records for each audit performed (currently over
300 records). Each record represents a single audit for an employee at
one of two sites. What I am hoping to do is bring all of the comments
recorded in the field [1:01 Scoring Comment] into a single field. So,
I would be able to see all of the comments for a given site in one
record line. Right now I have over 300 separate records with their own
comments. If I use the expression you suggested I can only group the
comments for an individual record, and not all of the comments from
all of the records.

As far as my table structure, it records things such as employee name,
employee number, manager name, site name, score for each question,
etc. In this case all I want to do is pull out the comments made by
the auditors, so that I can produce a report showing all of teh audit
comments made for all of the audits performed for employees at a
specific work site.

The full record set is actually contained in a query, due to a quirk
in the initial development. The site name, manager name, and employee
name are coming from the QA MSTM table, while the remaining fields are
coming from the QA Evaluation form table. These two tables are joined
through the employee number, which is common to both tables. This may
be part of my problem, considering the way the concatenation
expression is written. I am not sure how to write the query. In
essence, I only want the site names to appear in column 1, and in
column two I want to see all of the comments that were recorded. I am
hoping to generate only two rows, one for each site.

My apologies if I am being unclear, I am pretty new to this and
haven't quite gotten a handle on how to articulate my work in Access.
Thanks for your help!- Hide quoted text -

- Show quoted text -

This may also help to make things a little more clear, since I am not
particularly good at describing this in text. Here is the sql
statement for the query I am working on. This is based on Duane's
example, and modified based on some newsgroup suggestions for folks
who were receiving the same error I am. Unfortunately, when I run this
query I still receive the error message. I am sure this is just a
question of syntax, but as wet behind the ears newbie I am out of
ideas to resolve the problem.

SELECT [QA MSTM].Site, Concatenate("SELECT [1:01 Scoring Comment] FROM
[QA Evaluation Form] WHERE Site =""" & [Site] & """") AS
CustomerServiceScoring
FROM [QA MSTM] LEFT JOIN [QA Evaluation Form] ON [QA MSTM].[U #] = [QA
Evaluation Form].[U #];

 

[John W. Vinson]

. In this case all I want to do is pull out the comments made by
the auditors, so that I can produce a report showing all of teh audit
comments made for all of the audits performed for employees at a
specific work site.

If you are assuming that all of the memo fields must be concatenated
into a single field in order to produce this report, *your assumption
is incorrect*.

You can very easily create a Report printing out 300 - or 300000, if
you have enough paper - records' values of one or more fields.

Make it easier on yourself!

John W. Vinson [MVP]

 

[Isaac Martin]

If you are assuming that all of the memo fields must be concatenated
into a single field in order to produce this report, *your assumption
is incorrect*.

You can very easily create a Report printing out 300 - or 300000, if
you have enough paper - records' values of one or more fields.

Make it easier on yourself!

John W. Vinson [MVP]

That is part of the problem - I have been tasked with creating
something that is more condensed than that. One of my earlier attempts
a few days ago was to use the "Comment1] & chr(13) & chr(10) &
[Comment2], etc." style of expression, however it resulted in a report
that was around 64 pages in length. That is really my primary
motivation for trying to make the concatenation thing work. If I have
to I can do it the other way, it will certainly provide the data I
need to, but it comes out a little disorganized (since each record is
represented the comments aren't quite grouped in sequential order the
way I would like them to be) not too mention the length issue.

Believe me, I tried to sell them on the longer report, because I have
killed way too many brain cells trying to figure this out the past
couple of days. Anyhow, thanks so much for taking the time to reply, I
really appreciate your suggestions and feedback!

 

[John Spencer]

You don't need QA Evaluation Form in the main query. So I would just drop
that. Your query string that you are passing to Concatenate looks good
assuming that Site is a string field and never contains a quote mark.

SELECT [QA MSTM].Site
, Concatenate("SELECT [1:01 Scoring Comment] FROM
[QA Evaluation Form] WHERE Site =""" & [Site] & """") AS
CustomerServiceScoring
FROM [QA MSTM]

Trouble shooting.
Is Duane's concatenate function saved in a module that is named something
other than Concatenate? The function and the module cannot have the same
name.

If you are using an Access Project (.adp) did you make the changes to the
code that Duane specified?

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Duane's concatenation example involves concatenating the same field
from
several records. It sounds as if you are doing something else.

What do you mean by:
I want the query to concatenate the comments for each site using the
field
"1:01 Scoring Comment", which is a memo field

It sounds as if you are trying to store the result. In most cases you
will
not store a calculation (including concatenation). When you open the
query
in datasheet view, does a single row contain all of the comments you
want to
concatenate? If so, switch to query design view. At the top of a new
column put something like:
NewField: [Comment1] & chr(13) & chr(10) & [Comment2], etc.
chr(13) and chr(10) will move you to a new line for Comment2, in case
you
want that.

If this doesn't help, a little more detail is needed, including a brief
description of the table structure (with key fields and just a few
typical
fields).

My table holds unique records for each audit performed (currently over
300 records). Each record represents a single audit for an employee at
one of two sites. What I am hoping to do is bring all of the comments
recorded in the field [1:01 Scoring Comment] into a single field. So,
I would be able to see all of the comments for a given site in one
record line. Right now I have over 300 separate records with their own
comments. If I use the expression you suggested I can only group the
comments for an individual record, and not all of the comments from
all of the records.

As far as my table structure, it records things such as employee name,
employee number, manager name, site name, score for each question,
etc. In this case all I want to do is pull out the comments made by
the auditors, so that I can produce a report showing all of teh audit
comments made for all of the audits performed for employees at a
specific work site.

The full record set is actually contained in a query, due to a quirk
in the initial development. The site name, manager name, and employee
name are coming from the QA MSTM table, while the remaining fields are
coming from the QA Evaluation form table. These two tables are joined
through the employee number, which is common to both tables. This may
be part of my problem, considering the way the concatenation
expression is written. I am not sure how to write the query. In
essence, I only want the site names to appear in column 1, and in
column two I want to see all of the comments that were recorded. I am
hoping to generate only two rows, one for each site.

My apologies if I am being unclear, I am pretty new to this and
haven't quite gotten a handle on how to articulate my work in Access.
Thanks for your help!- Hide quoted text -

- Show quoted text -

This may also help to make things a little more clear, since I am not
particularly good at describing this in text. Here is the sql
statement for the query I am working on. This is based on Duane's
example, and modified based on some newsgroup suggestions for folks
who were receiving the same error I am. Unfortunately, when I run this
query I still receive the error message. I am sure this is just a
question of syntax, but as wet behind the ears newbie I am out of
ideas to resolve the problem.

SELECT [QA MSTM].Site, Concatenate("SELECT [1:01 Scoring Comment] FROM
[QA Evaluation Form] WHERE Site =""" & [Site] & """") AS
CustomerServiceScoring
FROM [QA MSTM] LEFT JOIN [QA Evaluation Form] ON [QA MSTM].[U #] = [QA
Evaluation Form].[U #];