Concatenating multiple values into a single field on a report

[Kevin]

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]

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

 

[Kevin]

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

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.

 

[Kevin]

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

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!

 

[Duane Hookom]

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

--
Duane Hookom
MS Access MVP
--

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?

 

[Duane Hookom]

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

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