If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Concatenating multiple values into a single field on a report
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 |
#3
|
|||
|
|||
Concatenating multiple values into a single field on a report
"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. |
#4
|
|||
|
|||
Concatenating multiple values into a single field on a report
"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! |
#5
|
|||
|
|||
Concatenating multiple values into a single field on a report
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 |
#6
|
|||
|
|||
Concatenating multiple values into a single field on a report
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 |
#7
|
|||
|
|||
Concatenating multiple values into a single field on a report
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 |
#8
|
|||
|
|||
Concatenating multiple values into a single field on a report
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 |
#9
|
|||
|
|||
Concatenating multiple values into a single field on a report
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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
STORE multiple values from a lookup table to another table. | beller | Database Design | 0 | June 15th, 2004 04:42 AM |
adding a field causes 90% of report to go away | cmpgeek | Setting Up & Running Reports | 6 | June 10th, 2004 08:47 PM |
HELP with updating a field after a report runs! | Jacqueline | Setting Up & Running Reports | 0 | May 27th, 2004 03:31 PM |
Listing multiple records in a single document | EHPorter | Mailmerge | 3 | May 27th, 2004 09:18 AM |
Adress field on report subform | Harmannus | Setting Up & Running Reports | 1 | May 26th, 2004 02:08 AM |