Removing duplicates from Access query

The real solution is not to include a 'multi-valued field' in the table, but, as said before, instead model the many-to-many relationship type in the conventional way by means of a table which resolves the relationship type into two one-to-many relationship types:

Sets----<SetMaterials>----Materials

The SetMaterials table will have two foreign key columns referencing the primary key columns of Sets and Materials respectively.

If you continue to use a 'multi-valued field', however, provided that you exclude the Material Table from a simple SELECT query it should not return duplicate rows.

The same principles would apply to use of the photo column if this as an 'attachments' field, but from its Name, Photo 1,  I suspect that you have three separate photo columns.  This would mean that the table is not normalized to First Normal Form (1NF), however, which requires that each row in a table should have only one value of each attribute.  Again the correct way to model this would be as a many-to-many relationship type with a Photos table, with the relationship type modelled by a third table as described above.  For examples of this you might like to take a look at Images.zip in my public databases folder at:

https://onedrive.live.com/?id=44CC60D7FEA42912%21169&cid=44CC60D7FEA42912

The zip archive contains a number of files of differing degrees of complexity for associating images with a record.  The basic Images.accdb file would probably be appropriate to your requirements.  Note that a key feature of this, and other files in the archive, is that the images themselves are not stored in the database, which can cause excessive bloat.  Instead the paths to the image files are stored in a column of text data type, and each image is loaded into an Image control at runtime by setting the ControlSource property of the Image control to the name of the column containing the path.

PhilG2165 

Unfortunately, your tables use Multi-Value fields. Ironically, they were introduced for the benefit of lesser experienced Access users,  but all too often, inexperienced people don't have the deep knowledge of how they work that is needed to understand them. Rather than benefiting new developers, in the long run, they actually cause more problems.  They add complexity to simple tasks like querying the data which would otherwise not raise their ugly heads.

Ken has already commented on this design problem, and offered a true solution, which is to resolve these tables into the proper many-to-many tables.

Like most experienced Access developers, I've never used them in a production database, so I don't have a handy example of how to work around the problem. Let's see if we can't come up with something for you, though.