I have an Access database to catalog a collection.  In the table “Sets”, there is a column that “Material Type” that links to another table (Materials.ID).  Material Type allows multi-selection to support cases where a set is made of two or more materials.  The problem is that when an item has multiple Material Types, the item shows up multiple times in reports.

For example:

In the table Sets, item 292 has two Material Types (Metal and Recycled)When querying, item 292 shows up twice.  This would make some sense if each result row shows a separate Material Type field (Metal in one, an Recycled in the other), but each result shows both values.Then, when I have a form based on the query, I get two duplicate items of 292:

So, my question is: How do I get rid of the duplicate rows in the query?  If the query only had one row, the form would only have one row. I’ve never been able to find any help or support articles that address this issue.

Thanks for any assistance you can offer.

Phil Garding

​I have an Access database to catalog a collection.  In the table “Sets”, there is a column that “Material Type” that links to another table (Materials.ID).  Material Type allows multi-selection to support cases where a set is made of two or more materials.  The problem is that when an item has multiple Material Types, the item shows up multiple times in reports. For example:In the table Sets, item 292 has two Material Types (Metal and Recycled)When querying, item 292 shows up twice.  This would make some sense if each result row shows a separate Material Type field (Metal in one, an Recycled in the other), but each result shows both values.Then, when I have a form based on the query, I get two duplicate items of 292:So, my question is: How do I get rid of the duplicate rows in the query?  If the query only had one row, the form would only have one row. I’ve never been able to find any help or support articles that address this issue. Thanks for any assistance you can offer. Phil Garding  Read More

​