Solved Unable to update query

[lodmark]

What you need for your tracks subform is whats called (I think I read that somewhere lol) is an auto lookup Query Like below

Which is what I use for my artists and recordings to show all recordings for the selected Artist

SELECT tblArtistsAndRecords.RecordID, tblArtistsAndRecords.ArtistID, tblRecords.Title, tblRecords.FormatID, tblRecords.Released, tblArtistsAndRecords.Main
FROM tblRecords INNER JOIN tblArtistsAndRecords ON tblRecords.RecordID = tblArtistsAndRecords.RecordID
ORDER BY tblRecords.Released;

Thank's @MickJav
I will definitely try this.
Sometimes....working with a radioprogram for the moment.
Leif

 

[lodmark]

1. You never corrected anyone who was suggesting other methods.
2. Your original db did not use a subform for artist because your relationship was 1-m rather than m-m.

Here is a sample that shows how a m-m relationship works from both sides.

Thanks for your input @Pat Hartman
I agree with you that I may not have answered everyone who suggested other solutions, but at the same time I have both with pictures and text and in the database presented what I did, so if you had looked closely you would have seen that in the three main forms as my database consists of there is always a subform, in two of them actually two subforms.
Your second statement is disproved by the fact that my db originally used these forms.
I agree with you that db from the beginning did not have an m-m relationship between artists and song (music) that is what I want to create now and then I get problems with the querys that the members of this forum have helped me create.
But I have also started to rebuild the whole structure and the querys that fill out the forms, but it is a lot of work.

I do know how relationships work. Even if I don't show it.

I wish everyone in the forum a Happy New Year!
Leif

 

[lodmark]

As recently as #30 (and I don't see anything newer), your relationship was still 1-m.

I'll go away now since you don't need my help.

Happy New Year.

Hi @Pat Hartman
I definitely need your help and others in this forum.
It's probably just that I'm having trouble expressing what I want to accomplish. I hope you want to come back and help me again.
The example in # 30 only refers to MickJavs picture, where I say that my solution with the tables regarding the connection to the radio programs is similar to his.
Below you see a picture of the db that I am trying to work with today (it is also in my first post in this thread).
But with this I get problems with my queries to the subforms.

There are several issues that need to be resolved.

1. An artist can have a song on several records,
2. a song can be made by several artists,
3. a song can be called the same but be different,
4. several artists participate in a compilation album,
5. e.g.

This is solved with the previous structure, but I have to ensure that it can be done even if I change to this structure.
And then I want to be able to put artists who do a duet (or several artists who do a song) on a line in the subform for records, for example with the help of Allen Browne's function.
This is my original problem in this tread.

Leif

 

[Dreamweaver]

I have a feeling you are seeing compilations that somebody with a lot more experience wont see.

I did post a ERD which would do 90% of what you want.

Before I started work on my DJ 2020 I asked for advice on the table structure which helped me stucture my system.

I do think you are stuggling with something that will never do what you want looking at it I can't make it work

I think your maybe trying to jump before you can walk what You want is a very complex beast that you will need to understand relationships far better than you do, asking for people to help do it for you will not help when you start coding the forms as you need a picture of how it all fit together.
I've edited my last ERD To include what you need to to with the tracks but even that is not complete.
hope it helps mick

 

[Dreamweaver]

Mic is tracking shows. You won't. If you were, you would also need a Show table.

Hi @Pat Hartman I added the Shows as in a earlier topic posted on the same topic as this one he had commented how he wanted to Create Playlists for his shows.
I did create this from a program I'm working on, I just hope he makes use of it this time but maybe it will help others

mick

 

[The_Doc_Man]

tbl_music still contains a normalization violation in that it has data for Artist1 and Artist2. If those fields are still active in your design, that is an error because surely if you have two artists, you could have three artists.

 

[Dreamweaver]

I've posted images before but you will see the result of the ERD I posted in relation to the Recordings

You'll Notice The Combination Artists Robbie Williams & Nicole Kidman and another below I dont create a many to many table between tblTrackNames And Artists because i designed with the combination artists in mind and to be honest that level of relationship was way more than I wanted to try and manage.

If you go here you will find a lot of images that might help DJ 2020 Gallery

 

[lodmark]

I have a feeling you are seeing compilations that somebody with a lot more experience wont see.

I did post a ERD which would do 90% of what you want.

Before I started work on my DJ 2020 I asked for advice on the table structure which helped me stucture my system.

I do think you are stuggling with something that will never do what you want looking at it I can't make it work

I think your maybe trying to jump before you can walk what You want is a very complex beast that you will need to understand relationships far better than you do, asking for people to help do it for you will not help when you start coding the forms as you need a picture of how it all fit together.
I've edited my last ERD To include what you need to to with the tracks but even that is not complete.
hope it helps mick

View attachment 87903

Thanks @MickJav
I'm very grateful for all the effort that you an everyone else has put in my problem.
I've used your latest ERD when i started to build the db all over again. But it's not finished yet.
I think I will take a step back and try a little bit harder by myself.
While I do this all your input ha been important.
Leif

 

[lodmark]

And several of us have told you this is wrong. It will NOT do what you want to do. We do understand your requirements.

When you put the artist ID on the record table, you are restricting the record to a SINGLE artist. That is the problem. There is a m-m relationship between artist and record. An artist can perform on many records and a record may have many performers. Therefore, you MUST have an intermediate table which is commonly called a junction table. This table is used to implement the TWO simultaneous 1-m relationships. It looks like tbl_artist_music is the junction table so you can connect multiple artists to each song.

You MUST remove Artist Id from the record table and you MUST remove both instances of artist id from the music table. You should also add a type CD to artist so you can distinguish musicians from singers if you want to. You can also add a roleCD to the tbl_artist_music if you want to know what each artist did on each song. You have other places where you have pathological relationships. In a 1-m relationship, the FK goes in the "many" table. Think of parents and children. parents can have many children but a child can have only one set of biological parents. That means that the ParentID goes into the child table. If you want to find the children of a parent, you join the parent table to the child table. If you try to put the ChildID on the parent table, how many slots are you going to have to provide? That's what you did in the music table. You added two instances of artist. Well, that might take care of a duet but it doesn't handle a trio or a quartet.

The sample I loaded shows how to use the junction table, tbl_artist_music from each direction. You will want to view the artists for a song and you will also want to view the songs performed by an artist. There is a summary in your case because you might want a list of artists for a record. That is a summary and you do that with a totals query which will return one row for each artist no matter how many songs he performed on the record. If he did them all, you'll get one row. If there were 12 songs done by 4 performers, you'll get 4 rows. If you ever find yourself adding columns to a table and prefixing (or suffixing) them, think again. this is almost always a repeating group and a repeating group is indicative of a 1-m relationship and the "group" needs to be rows in a new table rather than columns in this table. That allows you to have an infinite number of instances. Do NOT talk yourself into storing item1 and item2 because there's ONLY two of them. Do it right the first time. You are seeing this with the duet issue because there are not just duets as we all know.

Try to understand the model that Mic posted and do some reading on normalization. Your schema is incorrect and it is getting in your way. Just FYI, there are very few instances where relational models developed independently by different experts will differ in anything but minor details. Mic is tracking shows. You won't. If you were, you would also need a Show table.

Opinion: using long prefixes as you are is not productive in this environment where we have intellisense. When I was writing COBOL 50 years ago, I used prefixes but not today since the object itself provides the context. When you open your tables in DS view, it must be really annoying to never be able to see an actual column name. All you ever see are prefixes unless you make the columns extra wide.

Advice: the sooner you fix a problem (like removing the prefixes and fixing the schema), the less they will impede your development. Don't just throw up your hands because you think you are too far in to fix some things. It is really important to get your schema straight before you spend a lot of time developing form/queries/reports. Every object you make that uses a bad schema design will ultimately have to be changed. Get your foundation fixed so you can move on. Access has tools that will help you with column name changes. They are extremely dangerous so I always turn them off by default (Name AutoCorrect or AutoCorrupt as we call it) should ALWAYS be off UNLESS you actually want it to do something for you. What gets people in trouble is that they do not understand how/when the changes get propagated and so you can end up with quite a muddle if you are not careful. You have to change the names in an orderly way. Start with a backup and make several throughout the process. Change the column names in all the tables. Then open each object in the database to propagate the change. The final step is to compile and fix the compile errors caused by the name changes.

Thanks @Pat Hartman
Both because you returned but also for the detailed explanation.
I have some basic work to do and I will greatly benefit from your views.
It can also be the case that @MickJav writes that what I want to do, by putting the artists in a duet in a row in the same row, is too difficult work.
I have been bad at clearing in my ERD, the two columns for artist_ID in the music table were created in an attempt to create what I wanted, they are no longer used.
If I have understood you correctly @Pat Hartman, it is always better to create m-m tables than what I did in the relationship between the artist and record tables to have an artist_ID in the record table.
But it was then my problems started because I could not create an updatable query for the subform.

I will return for your comments when I have created an ERD with several m-m.

Leif

 

[lodmark]

tbl_music still contains a normalization violation in that it has data for Artist1 and Artist2. If those fields are still active in your design, that is an error because surely if you have two artists, you could have three artists.

Thanks @The_Doc_Man
They are no longer in use and need to be cleared out. My bad!
Leif

 

[Dreamweaver]

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.

 

[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

 

[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.