Forum Discussion
Removing duplicates from Access query
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 su...
Thank you for your response. I finally have time to return to this. When I try adding DISTINCT to the query, I get an error message: "The DISTINCT keyword cannot be used with the multi-valued field 'Sets.[Material Type]'.
My query is this (generated by the query designer):
SELECT Material.[Material Type], Sets.[Material Type], Sets.[Material Description], Origin.Continent, Sets.Origin, Origin.Country, Sets.ID, Sets.Name, Sets.[Acquired Year], Sets.[Number of Pieces], Sets.[Height of Tallest Piece (Inches)], Sets.[Material Type].Value, Sets.[Manufacturer/Artist], Sets.[Photo 1]
FROM Material INNER JOIN (Origin INNER JOIN Sets ON Origin.[ID] = Sets.[Origin]) ON Material.ID = Sets.[Material Type].Value
ORDER BY Sets.ID;
What I tried was to add DISTINCT after SELECT, like this:
SELECT DISTINCT Material.[Material Type], Sets.[Material Type], Sets.[Material Description], Origin.Continent, Sets.Origin, Origin.Country, Sets.ID, Sets.Name, Sets.[Acquired Year], Sets.[Number of Pieces], Sets.[Height of Tallest Piece (Inches)], Sets.[Material Type].Value, Sets.[Manufacturer/Artist], Sets.[Photo 1]
FROM Material INNER JOIN (Origin INNER JOIN Sets ON Origin.[ID] = Sets.[Origin]) ON Material.ID = Sets.[Material Type].Value
ORDER BY Sets.ID;
Is Even if I remove the reference to [Material Type], the error triggers on other fields. My photo field originally allowed three photos, and this seems to be tripping it, too:
Thanks,
Phil.
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.
