Solved Unable to update query

[Pat Hartman]

Only the subform method will allow you to add or delete artists. So, if this is an update form, you probably want to go with the subform.

 

[lodmark]

If you don't want to use a subform as Pat suggested (probably the best approach) you can use the "concatenate" function to put togheter all the names in one record, you should do it in the form in an unbound control if the record source returns more than one (current) record.

Microsoft Access tips: Concatenate values from related records

Return a string containing the values of related records.

allenbrowne.com

http://forestbyte.com/vba-code-samples/ (JoinFromArray).

Cheers,
Vlad

Thanks @bastanu
I have looked at that function and it works under certain conditions, like using a query because the two subjects in the where part must be in the same query or table.
Then it becomes a problem because I have to compare the names of the songs to find the artists and one song can be made by several artists without being a duet.
I have not yet found a way to pick out unique values here. Except when I create a junction table between Artist and Song (music).
But the work is progressing.
Leif

 

[Dreamweaver]

The only time I would use concentrated Values is:

1. The results Of A Search on the results screen "Used for visual Aid"
2. On A Report

 

[Pat Hartman]

Why do you not want to use a subform? No code is required when you take advantage of Access

 

[lodmark]

Why do you not want to use a subform? No code is required when you take advantage of Access

Hi @Pat Hartman
I do not know what gave you the impression that I do not want to use subforms.
The form I am struggling with contains a subform. It is the question that populates the subform that is the problem after I realized that I need to redo in my table relationships and create a junction table between artist and song (music).
My first post in this thread tries to explain this. The main form I work with (frm_record2) retrieves data from one query and then retrieves additional data to a subform with another query and it is this query that I can not add entries to with the new table and the new relationships.
You will find the database in my first post, feel free to look there.
Leif

 

[Dreamweaver]

What you need for your Recordings 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;

 

[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

 

[Pat Hartman]

I do not know what gave you the impression that I do not want to use subforms.

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.

 

[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

 

[Pat Hartman]

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.

 

[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

 

[Pat Hartman]

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

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.

 

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