Query for Many to Many Relationships

[hlm15]

Hi,

I am trying to set up a query based on a many to many relationship. I have attached a simplified version of my database. Currently I have a table recording information about Songs. As one song could be published for both the piano and the harp and the piano and harp are linked to more than one song, I have set this up using a junction table. For my query I want to display all the songs which are for the piano only. I have tried entering "Piano" into the criteria, but this results in the query displaying all the records which are for both the piano and the harp. What I really need, is to find a way to limit the criteria so that only the songs which has only one ID from the instrumentation table attributed to it (ie, just the piano). Is there a way to do this?



I do also need a list of all the songs which are for piano and harp, but without duplication the song ID. At present, if a song has been published for both the piano and the harp, the song also appears twice in the query. Is there a way to avoid this?


Thanks for your help.

[June7]

For Piano query, could use DCount and apply criteria where the count = 1. Also need to change the jointypes of the query:
SELECT Instrumentation.Instrumentation, Song.Composer, Instrumentation.Instrumentation, Song.[Song Title], Song.LibrarySigla
FROM Song RIGHT JOIN (Instrumentation RIGHT JOIN Song_Instrumentation ON Instrumentation.ID = Song_Instrumentation.Instrumentation) ON Song.ID = Song_Instrumentation.SongID
WHERE (((Instrumentation.Instrumentation)="Piano") AND ((DCount("SongID","Song_Instrumentation","SongID=" & [Song.ID]))=1));

For the Piano/Harp query, have to use the Instrumentation field as criteria only and don't retrieve the field and use either DISTINCT keyword or a GROUP BY (totals) query. Again, need to fix the jointypes:

SELECT DISTINCT Song.[Song Title], Song.Composer, Song.LibrarySigla
FROM Song RIGHT JOIN (Instrumentation RIGHT JOIN Song_Instrumentation ON Instrumentation.ID = Song_Instrumentation.Instrumentation) ON Song.ID = Song_Instrumentation.SongID
WHERE (((Instrumentation.Instrumentation)<>"Guitar" And (Instrumentation.Instrumentation)<>"Lyre"));