Solved Speed issue with ConcatRelated funktion

[lodmark]

Hi, I'm back!
Are there any solutions to the problems of slow response when asking questions to large tables with Alen Brunee's ConcatRelate function?

Leif

 

[Ranman256]

Ive never used (needed) ConcatRelate(), so I would advice against using it.

 

[Gasman]

You should be asking questions of your data first, then use ConcatRelated ?

 

[Pat Hartman]

Functions like this are not intended for use with large recordsets. What is the query for? Are you sure you actually need the function because you are exporting the recordset to Excel? If it is for a form or report, the many-side data should be in a subform or subreport. rather than the main query.

 

[jdraw]

lodmark,
Can you tell us more of the context and requirement? There may be options.

 

[lodmark]

OK, I'll try to describe what I want to accomplish.
At the end of 2020, I had two threads running about this.

Solved - Problems with duets!

When I started asking questions in this forum about how to create functionality in my database, I got a question about how I had intended to do with duets, ie songs with two artists. I then replied that I had intended to write the song with both artists. It now turns out to be a bad idea to do...

www.access-programmers.co.uk

and https://www.access-programmers.co.uk/forums/threads/duets-or-songs-that-have-several-artists.315102/
Since then, I have thought about different solutions and tested.
I have added an intermediate table in my original relationship image, tbl_artist_music.

Which allows me to create multiple artists on the same song.
The problem at the end of 2020 was that I placed this table incorrectly, between tbl_artist and tbl_record.
Now it is between tbl_artist and tbl_music (which contains the songs) I know that the names on the tables are a bit misleading which makes it difficult to follow.
I tested the solution with a few records with good results, nice and clean with several artists on the line for the song on a record.
But with a table that contains more than 3000 songs and is constantly increasing, this solution is not possible.
My hope is now returned to this group's knowledge and skills.
I enclose the database, unfortunately in Swedish.
Leif

 

[cheekybuddha]

Hi,

Unfortunately your db is in too new a version of Access for me to open, but have you considered using as listbox to display the artists.

You should be able to use tbl_music.musicID as the ControlSource, and something like the following as the RowSource:

SELECT
  a.artist_name
FROM tbl_artist a
INNER JOIN tbl_artist_music am
        ON a.artist_ID = am.artist_ID
WHERE am.music_ID = [music_ID];

(nb untested)

 

[Pat Hartman]

I have added an intermediate table in my original relationship image, tbl_artist_music.

This is not the way to do what you want. Given that artist is ON the record table which is what is causing the problem, you are limited to ONE artist per record. Since there can be more than one artist (once there is more than one, it makes no difference whatsoever how many there are) on a record, you CANNOT store the artist ID on the record table. You must create a junction table to manage the relationship.

Even worse than that is that by adding this incorrect relationship table, you can create relationships that violate your original relationship. This really isn't your answer.

I'm pretty sure I posted the solution in one of your other threads. The junction table must go between artist and record. An artist can make multiple records and a record can be performed by multiple artists. This is a many-to-many relationship. The problem is not just duets. It is trios and quartets as well or even larger groups.

I'm not sure why artist is in the music table. You have the exact same problem there which you have attempted to correct by adding a second instance of artist. As I said earlier, once you have more than one of something, you have many and to implement that correctly, you need a junction table. Think about this in a different context. A person might have children. In the US, the number of children per family is something like 2.3. We can't make 2.3 columns so you make 3. Both my mother and father came from big families. My mother had 4 siblings and my father had 6. I was speaking to a neighbor yesterday and turns out that her family had 14 children. 7 natural, 7 adopted. This is the exact relationship you are trying to model and you cannot do it correctly by simply adding additional fields with suffixes. Create the relationships correctly and other processes become much easier to implement. For some reason, you think that duets are the only problem. Even if they were, the correct solution is STILL a separate junction table.

Also, artist in the music table should actually be the songwriter rather than the performer although they could be the same person.

 

[cheekybuddha]

Also, artist in the music table should actually be the songwriter rather than the performer although they could be the same person.

Songwriter too should be a junction table since a song can often be written by more than one person

 

[Pat Hartman]

I thought I said that but I guess I wasn't clear

 

[lodmark]

Thank you for all your input.
And thank you @Pat Hartman You are quite right in pointing out that you gave me the solution already last year.
But either I did not understand it or it meant too great a change in what I, with your fantastic help, have already achieved.
I may have been stuck in an appearance on a form where I want the artists who make the song to be visible.

As for what you write about the songwriters, I have not intended to include these in the database, yet ....
The database contains my record collection but is also the basis for the content of my radio programs.
I have had a hard time finding time to work with the database during the spring, but this summer I found this picture of a relationship online.

That's when I got the idea of where to place the junction table.

I think maybe I need to start over from the beginning by creating a database that will be so good that it meets my small needs.
What you write @Pat Hartman that I can only have one artist per record is not correct, see the picture below.
But the solution in my existing database may not be perfect.

I continue to be grateful for all the tips.
Leif

 

[Pat Hartman]

You can create a string that concatenates all the individual artists if you use a properly normalized table schema. You can find a link in the similar threads below. You will have to normalize your schema to make use of it though.

If the artists in the music table are not the the people who wrote the music and lyrics, I don't understand the purpose of the table. There is no reason to include the artists (singers and musicians) in this table or even relate them to this table. The music stands alone. The performers are attached to the recording table. I wouldn't call it record since that implies a specific media.

I think maybe I need to start over from the beginning by creating a database that will be so good that it meets my small needs.

When you are developing for your self, you tend to piece things together as you think of requirements. You don't sit back and completely design - on paper- what you want. I do the same thing when working on a personal project. However, the difference is that I do this knowing that I will have to modify earlier parts if I missed something important. Adding fields here and there is trivial but changing relationships and primary keys can be more work since they will extend to forms, reports, queries, and code. BUT, I KNOW that I have to fix the bad stuff as soon as I realize the mistake because otherwise I'm building on quicksand and at some point, the whole project could collapse.

For professional projects, I do a lot more up front work to get the schema right before I ever build the first form or report. Even then sometimes I miss something but I rarely get relationships wrong and that is your problem here. That makes the most trouble to correct but you have to fix it or continue to build kludge after kludge after kludge.

 

[lodmark]

I have tried to create junction tables myself between the tables that had a connection to ID fields.
I then deleted these ID fields, record_artist_ID, music_artist_ID and music_artist2_ID.
It created some errors in the forms but these I am trying to fix.
Just hope I can save new records.
I tried to normalize the tables with access own tool but found no way to split the tables but referred to manual processing.
Curious about your point of view on the new relationship scheme @Pat Hartman, what more can be done?

 

[Pat Hartman]

1. If tbl_music truly is just about the sheet music, it should not contain media or song length. Those are a function of the actual recording. The media is where the sound is recorded and the length might vary slightly with differtnt interpretations by different artists or even the same artist in different recordings. That also means that it isn't related to the recording artists but if the artists who write lyrics and the score are in the same table as the musicians and singers, then this relationship is OK but the junction table should include their role. So you know that Joe wrote the score but Suzie and Jim wrote the lyrics.
2. When you have an autonumber in a table, it MUST be the primary key. There is no other reason for having an autonumber. You might also have a unique index. That would be the case for tbl_music_record. However, I have seen albums with two versions of the same song so I don't know what you would do about that.
3. Not all the tables have RI enforced. It should always be enforced to prevent orphan children.
4. tblmedia as I said in the first comment, does not have a relationship to tbl_music. If you want to know what media a piece of music is recorded on, you would find that out via the tbl_record. Having a second relationship is redundant and could lead to data anomolies since there is nothing that would force the relationship to be in sync.
5. Some tables have "ID" as the PK. Better practice is to give all PK's a name that relates to the table.
6. tbl_genre = same comment as #4
7. It makes more sense to me for tbl_Program_music to be tbl_program_record. Aren't you playing a specific recording on a program? If you want info from tbl_music, you can get there via tbl_record --> tbl_music_record --> tbl_music.
8. Whenever possible, the FK name should be the same as the PK name. You have deviations. This isn't possible when you have multiple references from the same table. For example, if you have Billing and Shipping addresses in the same table, the StateID would be BillStateID and ShipStateID
9. I probably mentioned this last year but using prefixes on column names is just noise and makes you type more characters before intellisense kicks in. It also becomes an anoyance when you open a table or query because all you see for every column is record, record, record. You have to widen the columns to see the actual column name. If you are welded to this concept, at least use suffixes so they don't get in the way.

 

[lodmark]

Thanks for your reply @Pat Hartman
First of all, I would like to apologize again for the names on my tables are so misleading.
Now take this in the right way, when it comes to vinyl records I know what I am doing.
@Pat Hartman as you write in your first comment you seem to have misunderstood what my database is supposed to contain. Today there is nothing about the songwriters or sheet music in my database.
But I am very grateful for your comments that I will try to apply to my database.

Here is an explanation of the tables.

• tbl_artist contains the artists who perform the songs, not those who have written the songs, even if it is sometimes the same person / s.
• tbl_music contains the songs, not the notes ie. what you call sheet music. Each song has a medium that can be an LP, a 45 or a 12 or maybe a CD or what I call SP ie Spotify and it is because I in my radio programs are sometimes forced to include songs that I do not have in my record collection. Had I not had the media in this table, I would not have known what the songs had for the media.
• tbl_record contains my records, not much to add there.
• tbl_program is a list of programs I made.
• tbl_program_date contains the date the programs were sent.
• tbl_program_music creates a connection between programs and songs in the programs.
• tbl_genre is of course the genre and is found like media on both records and songs.
• tbl_record_label contains the record companies.
• tbl_music_record creates a connection between the disc and the song.
• tbl_artist_music creates a connection between the artist and the song. (New)
• Finally, the table tbl_artist_record creates a connection between the artist and the record. (New)

When it comes to what you write about auto numbers (comment 2), it is completely correct and I should remove those that are not the primary key.
I do not agree with what you say in your comment no. 7. As I wrote earlier in this post, my programs can sometimes contain songs from Spotify, but sometimes also all songs from one and the same album.
I have made all the code work for the display in the forms but not to save new records. I have also not solved my original problem of getting the artists who do one and the same song to be shown on the same line.
Why is it so difficult? After all, it's just ones and zeros.
Leif

 

[Pat Hartman]

Apparently, I can't help you. Your schema is incorrect and you will have to continue to create kludges and more relationship errors. Good luck.

 

[lodmark]

Sorry to hear that @Pat Hartman
But still, with my new explanation of what the tables contain, I would like to see how you would make a correct schedule.
Maybe that would make me see my mistakes more clearly.
After that, maybe I can move on with my basic problem.
Leif

 

[Pat Hartman]

Your separation of "music" from "record" makes no sense to me. There is an object which is the original lyrics and score and that is associated with multiple people who write the words and the notes. "Music" usually starts out as notes on scraps of paper and ends up as "sheet music" as a finished product. It is a written representation of the song. Then you have a recording, which can be any type of medium and it represents the audio representation by specific artists. Your "music" is something else and I do not understand what it is or why it is in a separate table or why you have made a separate connection to artists.

But the problem you have come to us about more than once is how to deal with multiple artists which you think of as only duets which is certainly not reality. I have explained multiple times and at length how to correctly implement this relationship using a junction table but you seem to be happy with your kludges and I really don't have time to suggest kludges for you when I KNOW with a lifetime of experience how to solve the problem correctly, once and for all. It requires you to stand back and actually understand what a many-to-many relationship is but so far you haven't taken that step.

Vinyl may be your area of expertise but relational database design is mine and your design is incorrect because it violates first normal form which is the most basic level of relational database design.

I donate time here because I love Access and it gives me pleasure to help people to learn best practices and grow in their skill level. I do not donate my free time to help people build kludges. Period. It gives me no pleasure to do things wrong and it doesn't help you either. We have several "experts" who have nothing better to do than to build bad solutions for people. Perhaps one of them will help.

Do some reading about at least the first three normal forms. Define clearly the distinction between recording and music. Take two aspirin and call me in the morning

 

[lodmark]

Sorry but the headache is still here.
You sad that - "Your separation of "music" from "record" makes no sense to me."
I understand that it was a mistake to name the table containing the tracks on the records for tbl_music.
And it was also a mistake to write song instead of tracks in my earlier respond.
But that is what I did and so it will be.

I realize there are more compilations than duets, but I have to start somewhere. And I have created a junction table between artist and record as well as between artist and music (tracks) to make it possible to have more then one artist on each tracks or record.

I Still would like to se a scheme over how you would put the tables together.
Meanwhile I've change mine and perhaps it is now easier to follow.

I'm sorry we can not reach out to each other, maybe it's because of my poor English and because I explain poorly. But I'm really trying to understand what you mean.
Just as you have pointed out, I do not understand relational databases even though I have worked with them for almost 40 years but on a hobby level.
Before we end up in a situation where it becomes unpleasant, I think we draw a line over this discussion and I have to turn my questions elsewhere.
It has been nice here and I have received a lot of help, thank you for that.
Farewell
Leif

 

[Pat Hartman]

You have too many relationships and they are still not logical. Data must be stored at the lowest level where you need it. So if you want artist by track, it is not necessary to connect artists to a record and doing so violates normal forms since it leaves the data subject to anomalies. You can derive artists for a record by summarizing the artists by track.

media has noting to do with track. It is related to the physical record. There is a case for relating genre to track but the relationship is the same as for artists. If you put genre at the track level, you summarize it to get genre by record. Most records don't have different genre for different tracks although there is certainly some variability there. So again, if you keep genre by track, you should NOT keep it by record.

Also, I'm not sure that track is an entity in and of itself. You don't buy a track, you buy a record so track doesn't stand alone. That means that there is not a many-to-many relationship between record and track since a track only exists on a record. The relationship is 1-m and so you don't need the junction table. It's like defining a piano key as an entity. That makes sense if you are creating a parts database and building a bill of material for a piano but not for any other reason. A piano key does not stand alone. It is useless unless attached to a piano.

If the same song occurs on multiple records and the track data is identical or at least similar, you can create a copy function to reduce your data entry task.