Grouping on report

[Harley Feldman]

I have an Access database of patents. Each patent can be categorized in one or more of 9 categories, biological, mechanical, electrical, etc. I would like to generate reports by the assignee and grouped into categories, either in a "or" condition where all patents in each category are listed or in the "and" condition where the combination of categories is listed along with the patents that fit the and condition.

The problem is that due to the fact that patents can fit into one to nine categories simultaneously, each category has its own field in the patent record. I cannot figure out a grouping mechanism for both types of queries.

Harley

 

[Harley Feldman]

I should have mentioned that the category data types are "Yes/No".

Harley

 

[Guest]

Harley:

The problem is with the design of the table. Having separate Boolean
(Yes/No) columns for each category is what's called in the jargon of the
relational model 'encoding data as column headings'. The relational model
requires that data should be stored as value at column positions in tables
and in no other way. This is a common design error, but a design error none
the less, and as you've found out, causes problems.

The correct design would be to create a new Categories table with one row
for each category. The relationship between categories and Patents is
many-to-many as each patent can be in one or more category, and each category
can apply to one or more patents. A many-to-many relationship is modelled by
a third table, PatentCategories say, with two columns , e.g. PatentID and
CategoryID which reference the primary keys of the Patents and Categories
table. These two columns form the composite primary key of PatentCategories.

Data entry for this type of data is normally by means of a Patents form
based on the Patents table and a subform based on the PatentCategories table.
The subform would usually be in continuous form view and have one control, a
combo box bound to the CategoryID field but showing the Category text field
values looked up from the categories table. The combo box wizard can set
this up for you. The form and subform are linked on the PatentID fields.

For your report you simply join the three tables in a query and group the
report on the Category field.

If you retain your Boolean columns in the Patents table until the
PatentCategories table has been populated you can fill the latter quite
easily by means of a series of 9 temporary Append queries which , e.g. if the
Category ID for Biological is 1 then the query to create the rows for this
category would go like this:

INSERT INTO PatentCategories(PatentID,CategoryID)
SELECT PatentID, 1
FROM Patents
WHERE Biological;

Once you've run the 9 queries and are satisfied that the PatentCategories
table is correctly populated you can delete the 9 Boolean columns from the
Patents table.

Ken Sheridan
Stafford, England

 

[Harley Feldman]

Ken,

Thanks. I understand what you are saying. However, my user wanted to use
option buttons to select categories, not subforms which had me started on
the columns. I think that I can write code to translate the option buttons
to the Categories table and back.

Harley

 

[Guest]

Harley:

That would be the way to go. You'll need code in each option button's
AfterUpdate event procedure to insert a row into PatentCategories if its
turned one and delete a row if its turned off, and in the form's Current
event procedure to set the value of the option buttons for existing records.
The latter would simply be a case of looping through a recordset based the
PatentCategopries table for the current PatentID and setting the options
accordingly. Test for If Me.NewRecord when you do this and set all the
options off if the form has moved to a new record Else loop through the
recordset.

Let me know if you need any help with the code (and which version of Access
you are using).

Ken Sheridan
Stafford, England

 

[Harley Feldman]

Ken:

Thanks for the advice.

Here is another question. Assume that I have the following query:

SELECT ExternalPatents.EPatentID, EPatentCategories.PatentCategory FROM
ExternalPatents INNER JOIN EPatentCategories ON ExternalPatents.EPatentID =
EPatentCategories.EPatentID ;

If I set up the recordset to this query, then do I delete the records in
this query in the After Update event when an option is clicked off, or do I
delete individual records in the EPatentCategories table? The same question
on the add record side when the option button is clicked on.

I am using Access 2003.

Harley

 

[Guest]

Harley:

You insert or delete rows in the EPatentCategories table only.

Ken Sheridan
Stafford, England

 

[Harley Feldman]

Ken,

Thanks for your help. I have the application working exactly like you laid
out. I also learned good lessons for my next project.

Harley