I have two tables linked by a linking table. Table A contains a list of artists. Table B contains a list of works. The linking table allows a many to many relationship between the two. In other words, one artist can have many works, and one work can have more than one artist associated with it.
I want my main form to show the artist and the subform to show all the works associated with that artist. I have that set up fine and it works. The problem is that If an artist has 4 works, I have to step through the same artist 4 times (highlighting a different work on the subform each time) to get to the next artist. I have tried various combinations of joins to no avail.
What I want is for the artist to show up on the main form, all the associated works to show up on the subform (which it does now), but to have only one instance of the artist. When I advance to the next record in the main form, I should get a different artist, no matter how many works are associated with him/her. How do I accomplish that? Thank you for any help you may be able to provide.