If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|