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