I'm building a little Music Database with following tables:
I would like to sum up the total count of tracks in the Table 'albums' as calculated field. I created a Query with SQL that groups to albumtitles and shows the count of songs related to those albumtitles:
SELECT albums.AlbumTitle AS AlbumTitel, Count(songs.PK_songs_ID) AS TotalSongs FROM songs INNER JOIN albums ON albums.PK_albums_ID = songs.FK_album_ID GROUP BY albums.AlbumTitle;
That query works fine, but I don't know how to get the total count calculated in a calculated field. I need this field to be able to check in a formular if the tracklist is complete (the formular shows the artist and in a sub-form a list of all albums from this artist).
So, how do I get the 'TotalSongs' field in 'albums' working?