I'm trying to split one table into two, but I find that while my old query involving that table joined with a reference table allows me to edit and add records, a query joining the two new tables with the reference table does not allow this. I'm wondering if this is just too complex to still allow edits or if a better query would give me back that functionality.
DETAILS:
Originally I had a table called [Songs] and a reference table called [LPContents] that told me what albums each song in the table was on. I created a query called [Song query]
Code:
SELECT *
FROM LPContents INNER JOIN Songs ON LPContents.song_id = Songs.SongID;
If I run this query I am able to edit the rows or add a new row.
Then I split [Songs] into two tables, [Songs (Compositions)] and [Recordings] and created this query, called [Song Query (New)]:
Code:
SELECT *
FROM Recordings INNER JOIN ([Songs (Compositions)] INNER JOIN LPContents ON [Songs (Compositions)].SongID = LPContents.song_id) ON Recordings.song_id = [Songs (Compositions)].SongID;
In this latter case, I cannot edit or add rows. Is there any way to write this query so I'm able to add/edit?
(I should probably explain that the Join on [LPContents] is needed for when I query [Song query]/[Song query (New)] from a Subform that has access to an LPID variable. Like this:
Code:
SELECT *
FROM [Songs query (New)]
WHERE ((([Songs query (New)].[LP_id])=[LPID]))
ORDER BY [Songs query (New)].Side, [Songs query (New)].Track;
)
Here are abbreviated versions of the tables themselves, in case they're needed for clarity:
LPContents
Code:
SongKeyID
song_id
LP_id
Songs:
Code:
SongID
Title
ArtistID
Rating
Songs (Compositions)
Recordings:
Code:
RecordingID
song_id
ArtistID
Rating