PS: my issue to date has been that as soon as the Artist is linked to the ArtistPhoto, it then lists automatically the first Gallery and exhibition that Artist is also linked to rather than linking only to to Artist and then separately to whichever gallery and/or exhibition is specified, or to neither gallery nor exhibition if none are specified.
In your relationships, as designed, your ArtistPhotos records are not linked to ANY gallery. The artist is linked to galleries and there's no relationship to link a given photo to a given gallery.
Artists::__pkArtistID = ArtistPhotos::_fkArtistID
ArtistPhotos::_fkGalleryID = Galleries::__pkGalleryID
ArtistPhotos::_fkExhibitionID = Exhibitions::__pkExhibitionID
Your ArtistPhotos table is your actual join table linking artists to galleries and exhibitions by the photos shown in those locations/events.
A portal to ArtistPhotos on your Artists layout can include fields from the Galleries and Exhibitions tables or you can set up a list view layout based on ArtistPhotos and include fields from all three of the other tables in this layout.
Thank you very much for your answer.
If I relate everything as you suggest, is there a way to create a portal in Artists to show the Galleries the artist is showing in without it repeating the same gallery many times for every photo that is joined to that gallery? For now I created a portal to ArtistPhotos (renamed PhotosAndJoin) as in the attached image. The two drop down boxes are for creating a new record when needed by stating the FKartists and the FKGallery but this way it gives a line entry for every time a photo has been linked to a gallery so there just ends up a long list of the same gallery. I would like each different gallery to appear only once. Is there a Filter formula that could resolve this please?
All help is much appreciated.
Is a gallery ever associated (related) to an artist, or a photo, independent of an exhibition? (eg representation or residency)
Or is the relationship always in terms of an exhibition?
Yes a gallery will be related to an artist independent of an exhibition.
A gallery can also be related to an artist without a photo but rarely.
An artist can have many galleries, many photos, and many exhibitions.
Within all the photos, galleries and exhibitions an artist may have, some of those photos may be related to some of those galleries and / or some of those exhibitions.
A photo will have one artist and it may have a gallery and / or and exhibition.
So no, the relationship is not always in terms of an exhibition.
My original relationship diagram shown above was working great until I realised I needed sometimes relate both galleries and exhibitions (as "parents") to photos (as the "child")
With the relationships that I mapped out earlier, you can place portal to Galleries on your Artists layout and each gallery that is linked to at least one photo by that artist will be listed in that portal and without duplications.
You've clarified some things here. I have a data base for my own work, which has an additional Image table, whose records are either files or negatives, which then relate to Prints (which then have dimensions).
I'm inclined to think you might want a TO of Galleries to relate through Exhibitions (as in the venue of the Exhibition), which would be in the upper right corner of your pic, in addition to a separate relationship of the Gallery to the Artist or the Print. Managing multiple TO's for different relationships gets tricky, so I'm interested in seeing who else weighs in here, as I can usually make this work, but I'm not as experienced in this area as some others here (such as Phil).
Thank you so much again, as you say using the relationships you mapped out a Gallery, as opposed to a Photos, portal does the trick.
Thank you, I will bear your comments in mind incase I come across extra challenges: and here I was with a perfectly functioning database right up until I wanted to link more things to photos!!
Thank you both for your time and help.