Solved Unable to update query

[lodmark]

A Duet I think Is what I refer to as a Combination which I have tried to show you in other topics as well as this one

Christina Aguilera Feat. Lil' Kim
Is Made up of
Christina Aguilera
Lil' Kim
I always Add both as in Christina Aguilera Feat. Lil' Kim As it's easy for me to find all tracks By Christina Aguilera which would include the feat. tracks
To add another many to many table between tblTrackNames And tblArtists would in my view be unnecessary and would cause a lot of additional work with the design which I really don't think you would be able to manage.
Sorry to say but I wouldn't help if the continued to insist you needed to take that course.

Don't worry @MickJav I'm thinking of doing it exactly as you say.
However, in my list of tracks (tbl_music, (must change that )) I have a lot of tracks that I don't have in my vinyl record collection. These tracks, songs or whatever you want to call them is from my radio programs. Sometimes I have to choose songs that I don't own.
Those also needs to be related to the artist table. Because I also want to have my programs in the db, as you so nicely presented for me.
As it is now the track needs to have a relation to a record to be connected to a artist.
If I, as @Pat Hartman says, don't use the artist_ID in the music table I loose the relation with the artist. I could then create a m-m table, but as you say, it might create a lot of unnecessary problems for me.
This is what I've been doing today.
Leif

 

[Pat Hartman]

Properly normalizing the schema is NEVER more work than not normalizing it. I disagree with Mick's suggestion to not normalize. It is quite easy to locate duets, your query looks for tracks with an artist count of 2. If you want duets with Christine Aguilera (one of my personal favorites) include her as criteria by joining the "duets" query back to the m-m table. If you want duets of two specific people use an In() clause and include both their ID's. Obviously, the ID's are not hard coded, you need to join to the artist's table first if you are looking up by name.

Once you normalize the schema to properly handle duets, it also automagically handles trios and quartets. That is the power of doing it right the first time.

 

[Dreamweaver]

I do agree about mormalizing but the problem I have with my programs is the time it would take to add new entries and also the credit system for each recordings in this case we are talking about tracks as the recordings already use a m-m relationship.

I'm not disagreeing with you but you also have to assign things like if it's a Feat., With, And Credit plus which is the lead Artists I.E. Is on the left plus you might have 1-5+ artists involved in one credit.
most of my programs end up with millions of entries which have to be added as easily as possible.

When I built my origininal version of chart tracker neally 20 years ago people loved it but it had normalizion issues, but was quick to add the data which is what my users wanted one user has just upgraded from the original with one table holding just under 6 million entries he just complained about the exrtra time it was taking to input data as I no longer use combo boxes if I was to add the system you suggest it would make my system unusable even though the tables are normalized.

I have done everything I can but have to take into account the end users as they are the ones who will make it a success or flop even though it's normalized.

I am also concerned that the OP does not have the understanding at present to deal with the level of normalization you indecate my view is normalize it but up to a level where the OP can complete it otherwise he may end up giving up, I have seen 3-4 topics from this user, where he is still stuggleing with the basics. I'm just trying to help him at a level he can manage.

mick

 

[lodmark]

Properly normalizing the schema is NEVER more work than not normalizing it. I disagree with Mick's suggestion to not normalize. It is quite easy to locate duets, your query looks for tracks with an artist count of 2. If you want duets with Christine Aguilera (one of my personal favorites) include her as criteria by joining the "duets" query back to the m-m table. If you want duets of two specific people use an In() clause and include both their ID's. Obviously, the ID's are not hard coded, you need to join to the artist's table first if you are looking up by name.

Once you normalize the schema to properly handle duets, it also automagically handles trios and quartets. That is the power of doing it right the first time.

I have to study normalizing. And using the query in the way you suggest, that was interesting.
I hope you and @MickJav have agreed, I hate to be the one that got between you two.

I do agree about mormalizing but the problem I have with my programs is the time it would take to add new entries and also the credit system for each recordings in this case we are talking about tracks as the recordings already use a m-m relationship.

I'm not disagreeing with you but you also have to assign things like if it's a Feat., With, And Credit plus which is the lead Artists I.E. Is on the left plus you might have 1-5+ artists involved in one credit.
most of my programs end up with millions of entries which have to be added as easily as possible.

When I built my origininal version of chart tracker neally 20 years ago people loved it but it had normalizion issues, but was quick to add the data which is what my users wanted one user has just upgraded from the original with one table holding just under 6 million entries he just complained about the exrtra time it was taking to input data as I no longer use combo boxes if I was to add the system you suggest it would make my system unusable even though the tables are normalized.

I have done everything I can but have to take into account the end users as they are the ones who will make it a success or flop even though it's normalized.

I am also concerned that the OP does not have the understanding at present to deal with the level of normalization you indecate my view is normalize it but up to a level where the OP can complete it otherwise he may end up giving up, I have seen 3-4 topics from this user, where he is still stuggleing with the basics. I'm just trying to help him at a level he can manage.

mick

And I appreciate that!

Leif

 

[Dreamweaver]

Everybody has there own way of doing things I will be posting a ERD to a new topic to see if the issues I have can be solved which does relate to this topic just a very very advanced version of what you are doing.

@Pat Hartman I will post two ERD's orginal files one with my current solution and another the the advanced problem version maybe a few of us can solve the issues which I will try and be very detailed on

 

[Dreamweaver]

Thanks I think After working through this project and trying to help @lodmark with his issues I have now thought a way around the problems with what he calls duets but don't think duets is the main problem because it's not, the real problems are things like Genre, Collections, Dance types for my project. I woke a 4am today with what I think may be a way and will be carrying out a complete concept and creating a new ERD of my Chart Tracker Elite program I think I can now get around the problem with the extra time it will take adding "NEW" combinations But the downside is I think it will off the top of my head take me 2 years from the concept to final release.

 

[Dreamweaver]

@lodmark I've just added the 32bit version of DJ 2020 Which includes a version of Apollo my music Library.