Updating a table with a field extracted from a join

Okay, here's a fun SQL question that's got me stumped, but it takes a bit to
explain. I want to update a table with a value extracted from a join of two
others. The three tables are Music (a list of sheet music), Concerts (a list
of concerts) and SongPerformances (a list of the individual songs performed
at each concert). I want to update Music.LastPerformedSeason across the
board with the season of the most recent concert at which that piece was
performed, as found in Concerts.ConcertSeason. The problem is that because
each concert has more than one piece, the MusicID isn't in the Concerts table
at all, but rather in the SongsPerformed table. Simple enough to fix with a
join, right? I haven't been able to hit on the right one yet.

The relevant fields are (the first field in each is the primary key):

Music - MusicID, Title, LastPerformedSeason
Concerts - ConcertID, ConcertDate, ConcertSeason
SongsPerformed - SongsPerformedID, ConcertID, MusicID

What I want is an SQL query that will update the LastPerformedSeason (a
string formatted like "98-99") with the most recent season that song was
performed in, as found in the SongsPerformed table. For a given piece of
music, "most recent season" is the season in the same row as the highest
ConcertDate value in the Concerts table that joins (via ConcertID) to any
entry in the SongsPerformed table with that MusicID.

Unfortunately all the variations I've tried on this theme fail, most
commonly with a "must use an updateable query" message. I've got unique
indexes on everything possible, and there is no actual possibiliity that my
query can return more than one row per MusicID; nonetheless, that's what I
get. I'm not sure if Access can't handle anything this complicated or if I'm
doing it wrong.

If anyone has any suggestions, I would be grateful.

Thanks.

Patrick Bowman

I have not tested this setup, so don't use it on a good copy of your
database until you've tested it fully!


Create this query to return all the MusicID values and the ConcertSeason
value for the most recently performed concert for each MusicID that is in
SongsPerformed table -- let's name this query qryMusicIDLastPerformed:

SELECT SongsPerformed.MusicID,
Concerts.ConcertSeason, SongsPerformed.ConcertID
FROM SongsPerformed INNER JOIN
Concerts ON
SongsPerformed.ConcertID =
Concerts.ConcertID
WHERE Concerts.ConcertDate =
DMax("ConcertDate", "Concerts", "ConcertID=" &
SongsPerformed.ConcertID);



Then use this update query:

Update Music
SET LastPerformedSeason =
DLookup("ConcertSeason", "qryMusicIDLastPerformed",
"MusicID = " & MusicID);

--

Ken Snell
http://www.accessmvp.com/KDSnell/




"Patrick Bowman" Patrick wrote in message
...
Okay, here's a fun SQL question that's got me stumped, but it takes a bit
to
explain. I want to update a table with a value extracted from a join of
two
others. The three tables are Music (a list of sheet music), Concerts (a
list
of concerts) and SongPerformances (a list of the individual songs
performed
at each concert). I want to update Music.LastPerformedSeason across the
board with the season of the most recent concert at which that piece was
performed, as found in Concerts.ConcertSeason. The problem is that
because
each concert has more than one piece, the MusicID isn't in the Concerts
table
at all, but rather in the SongsPerformed table. Simple enough to fix with
a
join, right? I haven't been able to hit on the right one yet.

The relevant fields are (the first field in each is the primary key):

Music - MusicID, Title, LastPerformedSeason
Concerts - ConcertID, ConcertDate, ConcertSeason
SongsPerformed - SongsPerformedID, ConcertID, MusicID

What I want is an SQL query that will update the LastPerformedSeason (a
string formatted like "98-99") with the most recent season that song was
performed in, as found in the SongsPerformed table. For a given piece of
music, "most recent season" is the season in the same row as the highest
ConcertDate value in the Concerts table that joins (via ConcertID) to any
entry in the SongsPerformed table with that MusicID.

Unfortunately all the variations I've tried on this theme fail, most
commonly with a "must use an updateable query" message. I've got unique
indexes on everything possible, and there is no actual possibiliity that
my
query can return more than one row per MusicID; nonetheless, that's what I
get. I'm not sure if Access can't handle anything this complicated or if
I'm
doing it wrong.

If anyone has any suggestions, I would be grateful.

Thanks.

Patrick Bowman