more complicated structured wrecks ability to edit rows; any way around that?

[cherold]

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]

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)]:

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:

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

SongKeyID
song_id
LP_id

Songs:

SongID
Title
ArtistID
Rating

Songs (Compositions)

SongID
Title

Recordings:

RecordingID
song_id
ArtistID
Rating

[davegri]

I wouldn't divide the table into two tables. Just put a flag in the original file to indicate the type of each record.

[cherold]

Not sure what you're suggesting so let me explain what I'm trying to get to. The goal is to have a table of songs and one of song recordings. So Songs contains In My Life. Recordings has records for the song as recorded by the Beatles and another entry for the song as recorded by Judy Collins. In the old, working version, these two songs are unrelated.

The information you want for a written song (title, composer, lyrics) is substantially different from that you need for a recording (album, track #) which is why I divided them up.

[CJ_London]

I'm with davegri on keeping one table. But to answer your question - depends in what fields you want to be able add/edit but try using SELECT DISTINCTROW (the unique Records option in the query properties) - that may work, you may also need to change your joins to left joins as well. Basing forms on multi table queries is not recommended, generally speaking the rule should be one form one table which is accomplished using a main form with subforms.

[Micron]

Basing forms on multi table queries is not recommended, generally speaking the rule should be one form one table which is accomplished using a main form with subforms.

On that we do not agree.
If the idea is to normalize everything and we end up with multiple tables, what if my form needs fields from 5 tables? One main form and 4 subforms? Not me.

IIRC, any DISTINCT predicate will make a query read only. For a list of possible reasons, see
http://allenbrowne.com/ser-61.html

EDIT - To be clear, I'm not referring to a situation like ORDERS and order line items, which is where your statement would be most applicable. I'm referring to where you have many related tables that may or may not be involved in a 1 to many relationship like orders. I've seen that comment many times and to me, the interpretation is more all encompassing. Perhaps I read too much in to the intent, but I'm trying to guess how a novice would likely interpret that.

[June7]

Songs/Recordings relationship seems reasonable to me.

tblSongs
SongID
SongTitle
Composer (though this gets complicated when there are multiple composers given credit, and then the lyricist can be different from the music composer)
Lyrics

tblRecordings
RecordingID
fkSongID
fkArtistID
Rating

And more complication to associate albums with recordings.

tblAlbums
AlbumID
AlbumTitle
Studio

tblAlbumsRecordings
fkAlbumID
fkRecordingID

I have to agree that, in general, 1 form should edit 1 table. That form RecordSource can include lookup tables so related info can be displayed but fields from lookup tables should not be edited through that form.

[CJ_London]

On that we do not agree.
If the idea is to normalize everything and we end up with multiple tables, what if my form needs fields from 5 tables? One main form and 4 subforms? Not me.

I was referring to forms where there is a requirement to add/edit/delete

IIRC, any DISTINCT predicate will make a query read only

agree about DISTINCT, to be clear I was suggesting DISTINCTROW

[Micron]

agree about DISTINCT, to be clear I was suggesting DISTINCTROW

I saw that. To me, either would be a DISTINCT predicate so I take it that Allen's comment applies to both.

[cherold]

Just a quick note to say I figured this out. It was actually a stupid error because I was repurposing one table and, because of my dividing my other table into separate tables the repurposed table had fieldnames that didn't correlate with my new tables. So I wound up with two tables each joined through the same field in the third table.

So it was just pure sloppiness. Sorry to bother y'all with something so dumb!