Concatenating multiple values into a single field on a report

I have created a DB for tracking a company's trademark applications.
Trademarks are assigned to classes ranging from 1-45. Unfortunately a
trademark can be assigned to multiple classes. The company wanted easy
data entry, so I could not go with just a text field, so I tried combo
boxes based on values in a table. I stored the master trademark
information in the tblRegistrations table, and the class information
was stored in tblClasses which was linked to tblRegistrations in a one
to many relationship. So, one trademark registration can have multiple
classes assigned to it. For data entry I created a datasheet subform
where the user can select multiple classes for a trademark.

I need to be able to report the classes for a particular trademark in
a single field on a report (e.g. so the classes chosen will be
separated by a comma and it looks like a string like 1, 12, 16, 22,
37, 45). My initial work around was to create a crosstab query with
the classcode as the column header, the trademarkID as the row header,
and the classcode as the value. The result of this was to have 45
columns that were filled in with the classcode number if that class
was chosen for that particular trademarkID. I then created another
query which used the first query as its data source, and used the IIF
statement to see if there was a value in the 45 class fields, and if
so, then it would put in the class number and a comma, if not, it
would put in a null. Since queires only allows so many characters in a
field's code, I had to create multiple fields checking the values of
the class field from the first query. Then I had a field concatenate
the strings from those other fields, and it is this concatenated field
which I use in the report.

The big problem I am having is that all the extra querying is using a
lot of resources and slowing down running queries and reports based on
those queries.

Please, someone tell me an easier way to do this. I'm not much of a
coder, so solutions based on the basic functions of access would be
appreciated.

Thanks

There is a generic concatenate function that will do this (with examples) at
http://www.rogersaccesslibrary.com/O...p#Hookom,Duane

--
Duane Hookom
MS Access MVP
--

"Kevin" wrote in message
m...
I have created a DB for tracking a company's trademark applications.
Trademarks are assigned to classes ranging from 1-45. Unfortunately a
trademark can be assigned to multiple classes. The company wanted easy
data entry, so I could not go with just a text field, so I tried combo
boxes based on values in a table. I stored the master trademark
information in the tblRegistrations table, and the class information
was stored in tblClasses which was linked to tblRegistrations in a one
to many relationship. So, one trademark registration can have multiple
classes assigned to it. For data entry I created a datasheet subform
where the user can select multiple classes for a trademark.

I need to be able to report the classes for a particular trademark in
a single field on a report (e.g. so the classes chosen will be
separated by a comma and it looks like a string like 1, 12, 16, 22,
37, 45). My initial work around was to create a crosstab query with
the classcode as the column header, the trademarkID as the row header,
and the classcode as the value. The result of this was to have 45
columns that were filled in with the classcode number if that class
was chosen for that particular trademarkID. I then created another
query which used the first query as its data source, and used the IIF
statement to see if there was a value in the 45 class fields, and if
so, then it would put in the class number and a comma, if not, it
would put in a null. Since queires only allows so many characters in a
field's code, I had to create multiple fields checking the values of
the class field from the first query. Then I had a field concatenate
the strings from those other fields, and it is this concatenated field
which I use in the report.

The big problem I am having is that all the extra querying is using a
lot of resources and slowing down running queries and reports based on
those queries.

Please, someone tell me an easier way to do this. I'm not much of a
coder, so solutions based on the basic functions of access would be
appreciated.

Thanks

"Duane Hookom" wrote in message ...
There is a generic concatenate function that will do this (with examples) at
http://www.rogersaccesslibrary.com/O...p#Hookom,Duane

--
Duane Hookom
MS Access MVP
--

"Kevin" wrote in message
m...
I have created a DB for tracking a company's trademark applications.
Trademarks are assigned to classes ranging from 1-45. Unfortunately a
trademark can be assigned to multiple classes. The company wanted easy
data entry, so I could not go with just a text field, so I tried combo
boxes based on values in a table. I stored the master trademark
information in the tblRegistrations table, and the class information
was stored in tblClasses which was linked to tblRegistrations in a one
to many relationship. So, one trademark registration can have multiple
classes assigned to it. For data entry I created a datasheet subform
where the user can select multiple classes for a trademark.

I need to be able to report the classes for a particular trademark in
a single field on a report (e.g. so the classes chosen will be
separated by a comma and it looks like a string like 1, 12, 16, 22,
37, 45). My initial work around was to create a crosstab query with
the classcode as the column header, the trademarkID as the row header,
and the classcode as the value. The result of this was to have 45
columns that were filled in with the classcode number if that class
was chosen for that particular trademarkID. I then created another
query which used the first query as its data source, and used the IIF
statement to see if there was a value in the 45 class fields, and if
so, then it would put in the class number and a comma, if not, it
would put in a null. Since queires only allows so many characters in a
field's code, I had to create multiple fields checking the values of
the class field from the first query. Then I had a field concatenate
the strings from those other fields, and it is this concatenated field
which I use in the report.

The big problem I am having is that all the extra querying is using a
lot of resources and slowing down running queries and reports based on
those queries.

Please, someone tell me an easier way to do this. I'm not much of a
coder, so solutions based on the basic functions of access would be
appreciated.

Thanks

Thanks Duane. I had already tried and it keeps telling me "Undefined
function 'Concatenate' in Expression". So I'm getting nothing.

Perhaps if someone could tell me how to define 'Concatenate' then I
could try your instructions.

"Duane Hookom" wrote in message ...
There is a generic concatenate function that will do this (with examples) at
http://www.rogersaccesslibrary.com/O...p#Hookom,Duane

--
Duane Hookom
MS Access MVP
--

"Kevin" wrote in message
m...
I have created a DB for tracking a company's trademark applications.
Trademarks are assigned to classes ranging from 1-45. Unfortunately a
trademark can be assigned to multiple classes. The company wanted easy
data entry, so I could not go with just a text field, so I tried combo
boxes based on values in a table. I stored the master trademark
information in the tblRegistrations table, and the class information
was stored in tblClasses which was linked to tblRegistrations in a one
to many relationship. So, one trademark registration can have multiple
classes assigned to it. For data entry I created a datasheet subform
where the user can select multiple classes for a trademark.

I need to be able to report the classes for a particular trademark in
a single field on a report (e.g. so the classes chosen will be
separated by a comma and it looks like a string like 1, 12, 16, 22,
37, 45). My initial work around was to create a crosstab query with
the classcode as the column header, the trademarkID as the row header,
and the classcode as the value. The result of this was to have 45
columns that were filled in with the classcode number if that class
was chosen for that particular trademarkID. I then created another
query which used the first query as its data source, and used the IIF
statement to see if there was a value in the 45 class fields, and if
so, then it would put in the class number and a comma, if not, it
would put in a null. Since queires only allows so many characters in a
field's code, I had to create multiple fields checking the values of
the class field from the first query. Then I had a field concatenate
the strings from those other fields, and it is this concatenated field
which I use in the report.

The big problem I am having is that all the extra querying is using a
lot of resources and slowing down running queries and reports based on
those queries.

Please, someone tell me an easier way to do this. I'm not much of a
coder, so solutions based on the basic functions of access would be
appreciated.

Thanks

Forget my earlier post. I hadn't put in the Concatenate module from
your sample. Once I put that in there it worked like a charm. Duane
rules!

Hi Duane, I am using your concatenation module and it works great. Just one question - I get an error when try to use it more than once in my report. It works fine for only one usage, but I'm wondering if something in the ADO code needs to be tweaked to allow this function to be called simultaneously for two different report fields and two different SQL source statements?
thanks!

"Duane Hookom" wrote:

There is a generic concatenate function that will do this (with examples) at
http://www.rogersaccesslibrary.com/O...p#Hookom,Duane

--
Duane Hookom
MS Access MVP
--

"Kevin" wrote in message
m...
I have created a DB for tracking a company's trademark applications.
Trademarks are assigned to classes ranging from 1-45. Unfortunately a
trademark can be assigned to multiple classes. The company wanted easy
data entry, so I could not go with just a text field, so I tried combo
boxes based on values in a table. I stored the master trademark
information in the tblRegistrations table, and the class information
was stored in tblClasses which was linked to tblRegistrations in a one
to many relationship. So, one trademark registration can have multiple
classes assigned to it. For data entry I created a datasheet subform
where the user can select multiple classes for a trademark.

I need to be able to report the classes for a particular trademark in
a single field on a report (e.g. so the classes chosen will be
separated by a comma and it looks like a string like 1, 12, 16, 22,
37, 45). My initial work around was to create a crosstab query with
the classcode as the column header, the trademarkID as the row header,
and the classcode as the value. The result of this was to have 45
columns that were filled in with the classcode number if that class
was chosen for that particular trademarkID. I then created another
query which used the first query as its data source, and used the IIF
statement to see if there was a value in the 45 class fields, and if
so, then it would put in the class number and a comma, if not, it
would put in a null. Since queires only allows so many characters in a
field's code, I had to create multiple fields checking the values of
the class field from the first query. Then I had a field concatenate
the strings from those other fields, and it is this concatenated field
which I use in the report.

The big problem I am having is that all the extra querying is using a
lot of resources and slowing down running queries and reports based on
those queries.

Please, someone tell me an easier way to do this. I'm not much of a
coder, so solutions based on the basic functions of access would be
appreciated.

Thanks

I'm not familiar with this issue. Can you reply with your entire SQL view?

--
Duane Hookom
MS Access MVP
--

"BootieDay" wrote in message
...
Hi Duane, I am using your concatenation module and it works great. Just
one question - I get an error when try to use it more than once in my
report. It works fine for only one usage, but I'm wondering if something in
the ADO code needs to be tweaked to allow this function to be called
simultaneously for two different report fields and two different SQL source
statements?
thanks!

"Duane Hookom" wrote:

There is a generic concatenate function that will do this (with
examples) at
http://www.rogersaccesslibrary.com/O...p#Hookom,Duane

--
Duane Hookom
MS Access MVP
--

"Kevin" wrote in message
m...
I have created a DB for tracking a company's trademark applications.
Trademarks are assigned to classes ranging from 1-45. Unfortunately a
trademark can be assigned to multiple classes. The company wanted easy
data entry, so I could not go with just a text field, so I tried combo
boxes based on values in a table. I stored the master trademark
information in the tblRegistrations table, and the class information
was stored in tblClasses which was linked to tblRegistrations in a one
to many relationship. So, one trademark registration can have multiple
classes assigned to it. For data entry I created a datasheet subform
where the user can select multiple classes for a trademark.

I need to be able to report the classes for a particular trademark in
a single field on a report (e.g. so the classes chosen will be
separated by a comma and it looks like a string like 1, 12, 16, 22,
37, 45). My initial work around was to create a crosstab query with
the classcode as the column header, the trademarkID as the row header,
and the classcode as the value. The result of this was to have 45
columns that were filled in with the classcode number if that class
was chosen for that particular trademarkID. I then created another
query which used the first query as its data source, and used the IIF
statement to see if there was a value in the 45 class fields, and if
so, then it would put in the class number and a comma, if not, it
would put in a null. Since queires only allows so many characters in a
field's code, I had to create multiple fields checking the values of
the class field from the first query. Then I had a field concatenate
the strings from those other fields, and it is this concatenated field
which I use in the report.

The big problem I am having is that all the extra querying is using a
lot of resources and slowing down running queries and reports based on
those queries.

Please, someone tell me an easier way to do this. I'm not much of a
coder, so solutions based on the basic functions of access would be
appreciated.

Thanks

Thanks.
Actually had some time to research the problem further (I was in a rush when I posted yesterday...) and the problem is slightly different than I stated.

Here are the calls for the 1st and 2nd usage, respectively. First one works fine, 2nd one fails, though the query by itself works just fine. As you can see, both queries are structurally identical, selecting a single value from an INNER JOIN of two tables with the query parameterized by the same field. The only appreciable difference is the join condition on the 1st query is numeric and the join condition on the 2nd query is text(8) to text(10). Could this be the source of the problem?

=Concatenate("SELECT tblServicePack.Name & Chr(9) FROM tblServicePack INNER JOIN tblAffectedServicePack ON tblServicePack.apmServicePackID = tblAffectedServicePack.apmServicePackID WHERE tblAffectedServicePack.apmPatchID = " & [apmPatchID] & " ORDER BY tblServicePack.Name",Chr(13) & Chr(10))

=Concatenate("SELECT tblX_Language.Language & Chr(9) FROM tblX_Language INNER JOIN tblPatchLanguage ON tblX_Language.LanguageMnemonic = tblPatchLanguage.Language WHERE tblPatchLanguage.apmPatchID = " & [apmPatchID] & " ORDER BY tblX_Language.Language",Chr(13) & Chr(10))

First usage works just fine. Second usage fails with this error, failing at line 32:

"Run-time error '-2147467259 (80004005)':
Method 'Open' of object '_Recordset' failed



"Duane Hookom" wrote:

I'm not familiar with this issue. Can you reply with your entire SQL view?

--
Duane Hookom
MS Access MVP
--

"BootieDay" wrote in message
...
Hi Duane, I am using your concatenation module and it works great. Just
one question - I get an error when try to use it more than once in my
report. It works fine for only one usage, but I'm wondering if something in
the ADO code needs to be tweaked to allow this function to be called
simultaneously for two different report fields and two different SQL source
statements?
thanks!

"Duane Hookom" wrote:

There is a generic concatenate function that will do this (with
examples) at
http://www.rogersaccesslibrary.com/O...p#Hookom,Duane

--
Duane Hookom
MS Access MVP
--

"Kevin" wrote in message
m...
I have created a DB for tracking a company's trademark applications.
Trademarks are assigned to classes ranging from 1-45. Unfortunately a
trademark can be assigned to multiple classes. The company wanted easy
data entry, so I could not go with just a text field, so I tried combo
boxes based on values in a table. I stored the master trademark
information in the tblRegistrations table, and the class information
was stored in tblClasses which was linked to tblRegistrations in a one
to many relationship. So, one trademark registration can have multiple
classes assigned to it. For data entry I created a datasheet subform
where the user can select multiple classes for a trademark.

I need to be able to report the classes for a particular trademark in
a single field on a report (e.g. so the classes chosen will be
separated by a comma and it looks like a string like 1, 12, 16, 22,
37, 45). My initial work around was to create a crosstab query with
the classcode as the column header, the trademarkID as the row header,
and the classcode as the value. The result of this was to have 45
columns that were filled in with the classcode number if that class
was chosen for that particular trademarkID. I then created another
query which used the first query as its data source, and used the IIF
statement to see if there was a value in the 45 class fields, and if
so, then it would put in the class number and a comma, if not, it
would put in a null. Since queires only allows so many characters in a
field's code, I had to create multiple fields checking the values of
the class field from the first query. Then I had a field concatenate
the strings from those other fields, and it is this concatenated field
which I use in the report.

The big problem I am having is that all the extra querying is using a
lot of resources and slowing down running queries and reports based on
those queries.

Please, someone tell me an easier way to do this. I'm not much of a
coder, so solutions based on the basic functions of access would be
appreciated.

Thanks

I appreciate your use of Chr(9), Chr(13), & Chr(10)....
Try open the debug window (Press Ctrl+G) and enter all on one line and
substituting a valid value for XXX.
? Concatenate("SELECT tblX_Language.Language FROM tblX_Language INNER JOIN
tblPatchLanguage ON tblX_Language.LanguageMnemonic =
tblPatchLanguage.Language WHERE tblPatchLanguage.apmPatchID = XXX")
See if you can generate the same error. Try different values of XXX and
maybe put a break point in your code to step through and look for errors.
I assume apmPatchID is numeric?


--
Duane Hookom
MS Access MVP
--

"BootieDay" wrote in message
...
Thanks.
Actually had some time to research the problem further (I was in a rush
when I posted yesterday...) and the problem is slightly different than I
stated.

Here are the calls for the 1st and 2nd usage, respectively. First one
works fine, 2nd one fails, though the query by itself works just fine. As
you can see, both queries are structurally identical, selecting a single
value from an INNER JOIN of two tables with the query parameterized by the
same field. The only appreciable difference is the join condition on the
1st query is numeric and the join condition on the 2nd query is text(8) to
text(10). Could this be the source of the problem?

=Concatenate("SELECT tblServicePack.Name & Chr(9) FROM tblServicePack
INNER JOIN tblAffectedServicePack ON tblServicePack.apmServicePackID =
tblAffectedServicePack.apmServicePackID WHERE
tblAffectedServicePack.apmPatchID = " & [apmPatchID] & " ORDER BY
tblServicePack.Name",Chr(13) & Chr(10))

=Concatenate("SELECT tblX_Language.Language & Chr(9) FROM tblX_Language
INNER JOIN tblPatchLanguage ON tblX_Language.LanguageMnemonic =
tblPatchLanguage.Language WHERE tblPatchLanguage.apmPatchID = " &
[apmPatchID] & " ORDER BY tblX_Language.Language",Chr(13) & Chr(10))

First usage works just fine. Second usage fails with this error, failing
at line 32:

"Run-time error '-2147467259 (80004005)':
Method 'Open' of object '_Recordset' failed



"Duane Hookom" wrote:

I'm not familiar with this issue. Can you reply with your entire SQL
view?

--
Duane Hookom
MS Access MVP
--

"BootieDay" wrote in message
...
Hi Duane, I am using your concatenation module and it works great.
Just
one question - I get an error when try to use it more than once in my
report. It works fine for only one usage, but I'm wondering if
something in
the ADO code needs to be tweaked to allow this function to be called
simultaneously for two different report fields and two different SQL
source
statements?
thanks!

"Duane Hookom" wrote:

There is a generic concatenate function that will do this (with
examples) at
http://www.rogersaccesslibrary.com/O...p#Hookom,Duane

--
Duane Hookom
MS Access MVP
--

"Kevin" wrote in message
m...
I have created a DB for tracking a company's trademark
applications.
Trademarks are assigned to classes ranging from 1-45.
Unfortunately a
trademark can be assigned to multiple classes. The company wanted
easy
data entry, so I could not go with just a text field, so I tried
combo
boxes based on values in a table. I stored the master trademark
information in the tblRegistrations table, and the class
information
was stored in tblClasses which was linked to tblRegistrations in a
one
to many relationship. So, one trademark registration can have
multiple
classes assigned to it. For data entry I created a datasheet
subform
where the user can select multiple classes for a trademark.

I need to be able to report the classes for a particular trademark
in
a single field on a report (e.g. so the classes chosen will be
separated by a comma and it looks like a string like 1, 12, 16,
22,
37, 45). My initial work around was to create a crosstab query
with
the classcode as the column header, the trademarkID as the row
header,
and the classcode as the value. The result of this was to have 45
columns that were filled in with the classcode number if that
class
was chosen for that particular trademarkID. I then created another
query which used the first query as its data source, and used the
IIF
statement to see if there was a value in the 45 class fields, and
if
so, then it would put in the class number and a comma, if not, it
would put in a null. Since queires only allows so many characters
in a
field's code, I had to create multiple fields checking the values
of
the class field from the first query. Then I had a field
concatenate
the strings from those other fields, and it is this concatenated
field
which I use in the report.

The big problem I am having is that all the extra querying is
using a
lot of resources and slowing down running queries and reports
based on
those queries.

Please, someone tell me an easier way to do this. I'm not much of
a
coder, so solutions based on the basic functions of access would
be
appreciated.

Thanks

Hi Duane,
could never get the ADO to work with the 2nd query, so I went with DAO and it worked just great, with both queries, as is, embedded in the report.

oh well...

thx again!

"Duane Hookom" wrote:

I appreciate your use of Chr(9), Chr(13), & Chr(10)....
Try open the debug window (Press Ctrl+G) and enter all on one line and
substituting a valid value for XXX.
? Concatenate("SELECT tblX_Language.Language FROM tblX_Language INNER JOIN
tblPatchLanguage ON tblX_Language.LanguageMnemonic =
tblPatchLanguage.Language WHERE tblPatchLanguage.apmPatchID = XXX")
See if you can generate the same error. Try different values of XXX and
maybe put a break point in your code to step through and look for errors.
I assume apmPatchID is numeric?


--
Duane Hookom
MS Access MVP
--

"BootieDay" wrote in message
...
Thanks.
Actually had some time to research the problem further (I was in a rush
when I posted yesterday...) and the problem is slightly different than I
stated.

Here are the calls for the 1st and 2nd usage, respectively. First one
works fine, 2nd one fails, though the query by itself works just fine. As
you can see, both queries are structurally identical, selecting a single
value from an INNER JOIN of two tables with the query parameterized by the
same field. The only appreciable difference is the join condition on the
1st query is numeric and the join condition on the 2nd query is text(8) to
text(10). Could this be the source of the problem?

=Concatenate("SELECT tblServicePack.Name & Chr(9) FROM tblServicePack
INNER JOIN tblAffectedServicePack ON tblServicePack.apmServicePackID =
tblAffectedServicePack.apmServicePackID WHERE
tblAffectedServicePack.apmPatchID = " & [apmPatchID] & " ORDER BY
tblServicePack.Name",Chr(13) & Chr(10))

=Concatenate("SELECT tblX_Language.Language & Chr(9) FROM tblX_Language
INNER JOIN tblPatchLanguage ON tblX_Language.LanguageMnemonic =
tblPatchLanguage.Language WHERE tblPatchLanguage.apmPatchID = " &
[apmPatchID] & " ORDER BY tblX_Language.Language",Chr(13) & Chr(10))

First usage works just fine. Second usage fails with this error, failing
at line 32:

"Run-time error '-2147467259 (80004005)':
Method 'Open' of object '_Recordset' failed



"Duane Hookom" wrote:

I'm not familiar with this issue. Can you reply with your entire SQL
view?

--
Duane Hookom
MS Access MVP
--

"BootieDay" wrote in message
...
Hi Duane, I am using your concatenation module and it works great.
Just
one question - I get an error when try to use it more than once in my
report. It works fine for only one usage, but I'm wondering if
something in
the ADO code needs to be tweaked to allow this function to be called
simultaneously for two different report fields and two different SQL
source
statements?
thanks!

"Duane Hookom" wrote:

There is a generic concatenate function that will do this (with
examples) at
http://www.rogersaccesslibrary.com/O...p#Hookom,Duane

--
Duane Hookom
MS Access MVP
--

"Kevin" wrote in message
m...
I have created a DB for tracking a company's trademark
applications.
Trademarks are assigned to classes ranging from 1-45.
Unfortunately a
trademark can be assigned to multiple classes. The company wanted
easy
data entry, so I could not go with just a text field, so I tried
combo
boxes based on values in a table. I stored the master trademark
information in the tblRegistrations table, and the class
information
was stored in tblClasses which was linked to tblRegistrations in a
one
to many relationship. So, one trademark registration can have
multiple
classes assigned to it. For data entry I created a datasheet
subform
where the user can select multiple classes for a trademark.

I need to be able to report the classes for a particular trademark
in
a single field on a report (e.g. so the classes chosen will be
separated by a comma and it looks like a string like 1, 12, 16,
22,
37, 45). My initial work around was to create a crosstab query
with
the classcode as the column header, the trademarkID as the row
header,
and the classcode as the value. The result of this was to have 45
columns that were filled in with the classcode number if that
class
was chosen for that particular trademarkID. I then created another
query which used the first query as its data source, and used the
IIF
statement to see if there was a value in the 45 class fields, and
if
so, then it would put in the class number and a comma, if not, it
would put in a null. Since queires only allows so many characters
in a
field's code, I had to create multiple fields checking the values
of
the class field from the first query. Then I had a field
concatenate
the strings from those other fields, and it is this concatenated
field
which I use in the report.

The big problem I am having is that all the extra querying is
using a
lot of resources and slowing down running queries and reports
based on
those queries.

Please, someone tell me an easier way to do this. I'm not much of
a
coder, so solutions based on the basic functions of access would
be
appreciated.

Thanks