I have a Contacts table with a key called ContactID. I also have a document table to store attachments with searchable attributes. When a document is entered into the table via a Document form, the user has three fields where he/she can select a Contact that is associated with that document. For example: a document named "Joe Smith Incident Report 1Aug2018.pdf" is primarily regarding a client named Mr. Smith but may also involve two other clients like Juanita and Bobby. The user enters Mr. Smith into the first field that stores a ContactID and then also adds Juanita and Bobby into the other two fields storing ContactIDs.
How do I setup relationships or a query to check the [Documents]![IDContact1] for a match and then check [Documents]![IDContact2] and [IDContact3] to find matching records? I want to find ALL documents pertaining to a specific person [Contacts]![ContactID] that are stored in the Documents table in ANY of these three fields: IDContact1, IDContact2, IDContact3. I tried joining all three of those fields with a relationship to [Contacts]![ContactID] but the query that I created next thought that I wanted only records where the specific key showed in all three fields SIMULTANEOUSLY.
MS Access 2016
Win 10