I have two tables, linked through Artists Firstnames and surnames. One table has the artists BIOG (only one result per Artist.), the other table has the Artist's images (a lot of images per artist). I want to run a DISTINCT query showing the Artist's Image AND Biog. I am getting plenty of records with the code below, but cannot get the DISTINCT to filter out duplicates. I want the DISTINCT to work only on the Artist firstname and surname, but for it to disregard the images, which are all distinct.
Here is the code:
SELECT ArchiveCatalogues.ARTIST, ArchiveCatalogues.IMAGE, ArchiveCatalogues.FIRSTNAME, ArchiveCatalogues.SUFFIX, ArchiveCatalogues.TITLE, artist.surname, artist.firstnames, artist.dates, artist.honorific, artist.biog FROM ArchiveCatalogues INNER JOIN artist ON (ArchiveCatalogues.ARTIST = artist.surname) AND (ArchiveCatalogues.FIRSTNAME = artist.firstnames)
Any image would be acceptable at this stage. I was thinking that the main query should be the one that queries the table with the BIOGS, since each artist is distinct on this table (no artist is mentioned more than once.) Then, all I need to do is pull the image in from the other table.