Removing duplicates from Access query

[PhilG2165]

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:

1. In the table Sets, item 292 has two Material Types (Metal and Recycled)
2. 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.
3. 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

[#2]

Use the keyword DISTINCT in query SQL to remove duplicates.

[#3]

This results from the use of a 'multi-valued field', which is something most experienced Access developers counsel against. Using the DISTINCT predicate will make a query non-updatable, so this might not be suitable for a form's RecordSource. I would advise that, rather than using a multi-valued field, you model the many-to-many relationship type between Sets and MaterialTypes by the time-honoured method for modelling the relationship type by a table which resolves the relationship type into two one-to-many relationship types.
You might like to take a look at MVFCorrector.zip in my public databases folder at:

https://1drv.ms/f/c/44cc60d7fea42912/EhIppP7XYMwggESpAAAAAAABaDKZCllSuweYBPJ5zKa3cg

This little demo file illustrates how the database structure can be corrected by means of code in a form's module