Possible Need for Many to Many Relationship

This is a fun one.  We have goods guys and bad guys.  I assume that you are on the "good guys" side goin' after the bad guys.

Yes, you do need a middle table.  I think you need it like this


One Investigation has Many Observations. (It seems to me that you would want this relationship)
One Observation has Many Subjects Observed (this is one half of the join table)
One Subject has many Subjects Observed (this is the other half of the join table)

hence, you will need that new join table for Subjects Observed

In terms of your needs.

• Run a report that shows how many observations were done. So that sounds easy.
• 
  
• Run a report that shows the action taken on each Subject that was related to an Observation (action is MC Text field on the Subject Tables). I think that you would do this MC field on the Subjects Observed join table
• 
  
• Run a report that shows how many subjects were related to those observations. So you can easily tell how many Subjects Observed records there were for an Investigation, but the issue is if its the same bad dude Observed many times in many Subjects Observed, I'm guessing that you only want to count him once.  You could do an embedded summary report on the Investigations record of the Subjects Observed, and it would show you a list of the unique bad dude Subjects.