Solved Unable to update query (1 Viewer)

[lodmark]

Hi!
For some time now, I have been struggling trying to create the best possibility for my database to contain duets without creating duplicate entries in the table for artists (tbl_artist).
The best way to create this possibility I think is with a junction table between the song table (tbl_music) and the table for artists.
In the past I have had the artist table linked to the table for records because it seemed like the best option, see below. Now it needs to be moved and that means that the question (Query1, thanks to @MajP för this one) that governs the subform (frm_record2_subForm2) needs to be redone and this is where I have encountered problems.
Previous view of relationships. Current view of relationships.


Query1 as it looked before. Query1 as it is today, with the junction table.


Query1 works in both versions, except that I can not add new records in the later version where I added the junction table between tbl_music and tbl_artist.
And that's my problem for the moment.
I think I have managed to Google the reason why it does not work. The reason is that there are too many, many to many relationships in the query.
Unfortunately, I have not found a solution to the problem.

When I try to add a vinyl record by clicking on "New record" at the bottom of the form "frm_Record2" I get this picture, I can not add any songs to the record, The box for the subform is empty.

As usual, you will find my database to download if you want to look further into my problem.

Thanks in advance for all the help and wishing you a HAPPY CHRISTMAS!

Leif

 

[Dreamweaver]

You will continue to have problems as your tables are not linked correctly and I have noticed you have 3 keys on the tbl_artists_music the ID field should only be a auto number, I only include a autonumber as it makes deleteing and working with each record simpler.

 

[Dreamweaver]

P.S. I'll be starting to create my ERD for my music Library which I'm calling "Apollo" This will include most of the music sections in DJ 2020 So will be less confussing.

 

[Pat Hartman]

Your relationship between record and artist is incorrect. There is a many-many relationship and that means you need a junction table. Remove ArtistID from the record table and add a junction table to solve the problem. If you want to distinguish a lead vocalist, you can add an indicator to the junction table.

Artists can be vocalists or musicians so the junction table handles that also. The role artists play can be kept in the junction table if that is important to you.

 

[lodmark]

Your relationship between record and artist is incorrect. There is a many-many relationship and that means you need a junction table. Remove ArtistID from the record table and add a junction table to solve the problem. If you want to distinguish a lead vocalist, you can add an indicator to the junction table.

Artists can be vocalists or musicians so the junction table handles that also. The role artists play can be kept in the junction table if that is important to you.

Thanks @Pat Hartman I can see that. But the junction table will only hold the relations between the artist and the record. How do I with that junction table create a relation between two artists and a song (music in my database) like a duet? Or do I have to have a junction table between the music table and the artist table as well?
And if so I'm back to my question above......

Leif

 

[The_Doc_Man]

When dealing with junction tables, you EXPECT a many-to-many relationship from either side of the junction. Therefore, if you have a junction between singer(s) and specific song(s), then if you have 2 singers to 1 song, you MUST have a duet. Similarly, three singers is a trio, four singers is a quartet, five singers is probably Pentatonix...

 

[lodmark]

When dealing with junction tables, you EXPECT a many-to-many relationship from either side of the junction. Therefore, if you have a junction between singer(s) and specific song(s), then if you have 2 singers to 1 song, you MUST have a duet. Similarly, three singers is a trio, four singers is a quartet, five singers is probably Pentatonix...

Thanks @The_Doc_Man for your explanation. Of course, there is a relationship one to many and many to one. That is why you have a junction table. And my goal is to set it up as you describe it, but I'm not sure how I fill in that it is a duet or a trio and how I should present it in my forms.
Then my question remains about why I can not make Query1 updatable?

Leif

 

[Dreamweaver]

The duet or a trio Relate to the Artist/Groups see picture
Notice the artist/group Types list

 

[lodmark]

P.S. I'll be starting to create my ERD for my music Library which I'm calling "Apollo" This will include most of the music sections in DJ 2020 So will be less confussing.

Maybe I'm asking really stupid questions but what's an "ERD"?
I have created an account on your website @MickJav to be able to download the database called "Disc Jocky Bookings And Music Library" but it is a .drawio file that I can not open.
I would be grateful if there is an opportunity to see what you have done more "hands on", maybe in access?
I'm looking forward to see the "Apollo".

Leif

 

[lodmark]

The duet or a trio Relate to the Artist/Groups see picture
Notice the artist/group Types list

The picture shows the form for all a group's recordings and if I understand you correctly, it would have been shown in the box for "Artist Types" if the group was a duo or similar.
What I want to achieve is that for a compilation album that contains a song sung by two artists, both artist names are shown in the field for artist. As this picture shows.

Now I have instead put the artists who made the song on the record together, which resulted in me getting everyone as a group in my table for artists. In this way.

I want the two artist to be separate entries in the artist table and to be written together in the form when the act as a duo.
If I then search for Bee Gees or Paul Nicholas, I get the song on both artists, I do not get it with the solution I have today.

Leif

 

[Dreamweaver]

Kylie Minogue & Jason Donovan is a combination artist you also need two other entries for Kylie Minogue and jason Donovan as below

 

[lodmark]

Kylie Minogue & Jason Donovan is a combination artist you also need two other entries for Kylie Minogue and jason Donovan as below
View attachment 87671

Am I right @MickJav if I interpret your picture above as that you must have both Kylie, Jason and Kylie and Jason as three unique items in the table for artists?

Leif

 

[Dreamweaver]

It's they way the credit system works within the official charts as that song/album has been credited to both so I use the full credit name then each artist a lead artist gets say 100 points and a normal (Arist wothout lead ticked) Gets 50 and a Combo gets 0 that is if I'm using a credit system

 

[Dreamweaver]

You can see a lot of the data for one of my programs outputted to pdf files here: https://databasedreams.createaforum.com/weekly-charts-analysis/

 

[The_Doc_Man]

but I'm not sure how I fill in that it is a duet or a trio and how I should present it in my forms.

The question first is whether the song is specific to an album or collection or whatever. If you have the ID for the song, can you trace it back to a specific origin? (I haven't looked at the diagrams that hard.) If so, then the next question is whether there is EVER a time where you would have two vocalists on a single identifiable song and NOT have either a duet or a named combo? And in the case of a named combo would you name the members individually anyway?

When I did my CD database some years back, I took the approach called entity-attribute-value (EAV) layout. My structure was more or less:

Recording (RecID, media format, album title, record label, serial number, year released, and a couple of other lesser details)

Selection (SelID, RecID as FK, OrdID as the position in the sequence of selections, title, duration in min:sec as a DATE field, a couple of other things) OrdID was 1, 2, 3, etc. representing 1st track, 2nd track, ...

Details (DetID, SelID as FK, Detcode i.e. what was represented, text value of same) This was the EAV table where <SelID> was the entity, <DetCode> was the attribute, and then the value was literal text.

DetailCodes( DetCode, name of thing being represented)

The detail codes were things like "Artist" "Group" "Composer" "Soloist" (used when "group" represented an orchestra) "Accompanist" "Cover Artist" (when I was feeling whimsical or the cover art was unusual), etc. The DetailCodes table included a "sort order" value that said put Artist, Group, or Orchestra first, Conductor second, Soloist third, etc. I had a hierarchy of presentation order.

To be honest, I didn't care that I had a duet, but I could have annotated that through the EAV table by having an attribute "DUET." That property would be present (with a blank value) if the song was a duet, but absent if it was a two-person group like "Hall & Oates." H&O billed themselves as a combo, not two independent singers in "duet" style. Case in counterpoint, Andy Bennett and K.D.Lang's album where they clearly were as a duet.

Note that this wasn't a "PURE" EAV because in a pure EAV, the title and duration that were on the "Selection" record would actually have been in the EAV table as two more attrbutes. But I made some placement decisions for report convenience.

 

[Pat Hartman]

Whenever you have "many" of something, you need a subform. Use a subform to show the artists rather than a single combo. The artist field in the subform will be the combo you now have on the main form.

If you really need to see the word "duet", use a dcount() on the artists for the song. If the number is 2, it is a duet, 3 a quartet, etc.

 

[Dreamweaver]

I'm going to bail out as I can see this getting far to complicated for the OP He's struggling with basics

 

[lodmark]

I'm going to bail out as I can see this getting far to complicated for the OP He's struggling with basics

OP?

 

[The_Doc_Man]

The "Cannot update query" problem frequently occurs when you try to update through a display query that, via JOINs, enumerates multiple selections. Access is smart, but if it gets confused, it will refuse to update. When that happens, you need to have a separate query to do the update that targets only the specific record to be updated and omits stuff from JOINed tables that wouldn't be updated.

A display/enumeration query is great for reports but often sucks for updates.

EDIT: "OP" is forum slang for "Original Poster"

 

[Dreamweaver]

Not you @lodmark I've been trying to keep it simple as I understand your level hence the pictures What I will do is put A Basic ERD, Some of th posts we're getting close togoing over my head.