The specifics of this will depend on how your table is setup.
Presumably you have 3 tables like this:
- Category table, with CategoryID primary key.
- Patent table, with PatentID primary key
- PatentCategory table, with fields:
PatentID foreign key to Patent.PatientID
CategoryID foreign key to Category.CategoryID
If so, you might be able to add another field to the Category table (say,
CategoryValue), and give each record a value that is a power of 2, e.g.:
1 Biological
2 Electrical
4 Mechanical
8 Pharmaceutical
Then you can create a query using the 3 tables.
Group By PatentID
Sum this CategoryValue field.
Save the query.
Then add this query as a source "table" in your existing query.
Join it on PatentID, and add SumOfCategoryValue to the query output.
You will now be able to group your report on SumOfCategoryValue, which will
give you a different grouping for each combination, e.g.:
- Biological only will be 1.
- Biological + Electrical will be 3.
- Biological + Electrical + Mechanical will be 7.
- Biological + Electrical + Mechanical + Pharmaceutical will be 15.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Reply to group, rather than allenbrowne at mvps dot org.
I have a patent report grouped by four categories - biological, electrical,
mechanical and pharmaceutical. The problem that I cannot seem to figure out
is how to categorize the report by all combinations of categories - by those
patents that are both biological and electrical, by those which are
biological, electrical and mechanical, by those having all four categories,
etc. Any suggestions on how to create this grouping?
Harley
