multiple many-to-many relationships

[barbP]

Hi - in my db I track information from research articles about climate hazards and how people respond.


there are two many-to-many relationships: an article might mention many hazardEvents and each hazardEvent can be mentioned in many articles.
for each HazardEvent there can be many ways people respond (=ResponseTypes)
and each responseTYpe can be used for many HazardEvents.
Below is a screenshot of a query to return for each paper the hazard event and the types of responses.
Here is my problem: in cases where the same hazEvent is mentioned in several papers but different responseTYpes are mentioned, the query returns a record for each responseType and links it to each article. But I only want the responseType that is mentioned in the article.
What is the reason for this incorrect association and what can I do to fix it?

[rpeare]

I don't think your data structure is quite right. If you want to record the ResponseType associated with each paper's article you would have to have another table that listed the REFERENCEMM ID and the ResponseType_ID Then your RESPONSEMM would be linked to this new table and NOT to the HAZARD_EVENTS table. You are having trouble because you are not relating the different responses mentioned in each paper's articles.

[barbP]

Thanks - yes - I see this now. I have changed my design. I added the ResponseTYpeID to the referenceMM and deleted the responseMM. That simplifies the design and also reflects the fact that both hazardEvents and Responses are referenced in the articles.