Can't update a field from a query result due to 'Group By'

[MattGreer]

Using MS Access 365. I have a query that counts the number of times a song has been performed given a date range entered by the user (just me). What I would like to be able to do, however, is update a Yes/No field from within that query. I went about a bit of trial and error and it seems that the aggregate function (if I'm using that term correctly) 'Group By', which seems to be required for this query to run, prevents any updates to that particular field. (The field in question is 'SheetMusic'.)

Is there a way to create a query that would allow me to update that field?

SELECT Count(tblSongsPlayed.SongID) AS [Song Count], tblSongs.Title, tblSongs.SheetMusic
FROM tblSongs INNER JOIN (tblEvents INNER JOIN tblSongsPlayed ON tblEvents.EventID = tblSongsPlayed.EventID) ON tblSongs.SongID = tblSongsPlayed.SongID
WHERE (((tblEvents.Date)>=DateAdd("yyyy",-1*[How Many Years Back Do You Want To Go?],Now())))
GROUP BY tblSongs.Title, tblSongs.SheetMusic
ORDER BY Count(tblSongsPlayed.SongID) DESC;

Screenshot of the 'Design View' in MS Access:

https://i.imgur.com/Po3hkyD.png

Thanks!!

Matt

 

[combo]

You can use two tables on the right (screenshot) to perform calculations in a separate query, in new query link it with tblSongs without grouping.

combo

 

[dhookom]

I wonder if grabbing the total from a subquery would work.

SELECT tblSongs.Title, tblSongs.SheetMusic, 
(SELECT Count(tblSongsPlayed.SongID) AS [Song Count]
 FROM tblEvents INNER JOIN tblSongsPlayed ON tblEvents.EventID = tblSongsPlayed.EventID 
 WHERE tblSong.SongID = tblSongsPlayed.SongID AND tblEvents.Date>=DateAdd("yyyy",-1*[How Many Years Back Do You Want To Go?],Now())) AS SongCount
FROM tblSongs;

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016