Triplicate Records Being Displayed by Query

[nonakag]

Another reason not to use multivalue fields of which attachment fields are a subtype

Your attachment field is made up of 3 parts: FileData, FileName, FileType. Together these manage the MVF component of the field
You can use DISTINCT with FileName and/or FileType but not with FileData or the attachment field itself.

However, now I've seen the full SQL including the use of MVFs/attachments, I would be very surprised if using DISTINCT on these will solve your problem.

Isla, you are correct. When I added the Destinct, it pushed and error that the attachment field is not compatible. Is there a way to create a button to a popup form for attachments for each unique record so that way I could remove the direct attachement field from the query? Thank you.

 

[isladogs]

I would strongly recommend removing all attachment fields (and any other MVF fields)
Basically you need to store the files outside Access and store just the file path in Access with a separate record for each file.
This will drastically reduce your database file size and should improve performance

If you only have a few attachments, it is easy enough to start again using the above approach
However, if you already have a lot of attachments, you may wish to automate that process.
Doing that is a separate topic in itself which needs its own thread.

 

[Pat Hartman]

When you have data in a 1-many relationship, you use a main form to show the 1-side data and a subform to show the many-side data.